What is a "Partial Matching Index"?

Question

I'm trying to learn more about the "foreign key references check" query plan operator introduced in SQL Server 2016. There's not a lot of information about it out there. Microsoft announced it here and I blogged about it here. The new operator can be seen by deleting a row from a parent table with 254 or more incoming foreign key references: dbfiddle link.

There are three different counts displayed in the operator details:

• Foreign Key References Count is the number of incoming foreign keys.
• No matching Indexes Count is the number of incoming foreign keys without a suitable index. Verifying that the updated or deleted table won't violate that constraint will require a scan of a child table.
• I don't know what Partial Matching Indexes Count represents.

What is a partial matching index in this context? I wasn't able to get any of the following to work:

• Filtered indexes
• Putting the foreign key column as an INCLUDE column for an index
• Indexes with the foreign key column as the second key column
• Single column indexes for multiple column foreign keys
• Creating multiple covering indexes to enable an "index join" plan for a multiple column foreign key

Dan Guzman pointed out that multiple column foreign keys can match indexes even if the index keys are in a different order than the foreign key columns. His code is here in case someone is able to use it as as starting point to figure out more about partial matching indexes.

Answer 1

I spoke to people much smarter than I and we will be documenting this soon™.

The actual definition of this, in the interim is:

PartialMatchingIndexCount reflects the number of references that could be checked using index seeks, but the index key does not cover all the columns being checked. For example, the corresponding ForeignKeyReferenceCheck elements contain both a Seek Predicates and a Predicate element.

In addition:

If this number is greater than 0, there is a potential performance issue, in case the partial matches result in large numbers of rows.

Answer 2

After some more googling, I managed to come up with a post that mentions "Partial Matching Indexes" and Foreign Keys

The March 1, 2013 Blog Post in Carlos Klapp's Code Blog entitled Foreign Keys without Indexes has the Stored Procedure called Util_FKsWithoutIndexes which looks for foreign keys that does not have proper indexes for FK relationships. (Looks like this blogger lifted this from SQL Server Central's The Ultimate Index-Less Foreign-Key Finder (Oct 15, 2009)) The blog says the following:

Searches for foreign key constraints that don't have fully matching indexes.

The best partial matching indexes are outputted with MatchCounts and column comparisons

Generates a CREATE INDEX template for each foreign-key with no matching index or a partial-matching index.

Customize as needed (add includes, if you want it clustered, or if it should be a part of the primary key, or if you want to merge with another index)

FKs missing full matching indexes can severely hurt the performance of DELETES on the referenced table due to table-scans for checking referential integrity, as well as SELECTS on the referencing tables where the foreign-key column(s) are in the WHERE or JOIN predicates (This will affect you whether a constraint exists or not). This only checks the first N columns of the index where N is the number of columns in the foreign key constraint.

Index column order is not verified beyond this (A two-col FK that has 1 matching column in the 2nd col of a 3-col index will be outputted as a partial match)

If your database has no foreign key constraints, then this tool will be worthless to you.

Many databases have partial foreign key constraint coverage. This only works on related tables where constraints are declared.

If I understand this correctly, if a FK relationship does not have an index that matches every column in some needed index, there may be some indexes that have some of the columns. For example, if a FK relationship has three columns (a,b,c) but there is no index with those same three columns, there may exist an index that has (a,b) or (a,c) or (b,c) and may help with queries but will require some index scans to rows with the missing column.

If there are no indexes at all that can support a FK constraint, then the "Partial Matching Indexes Count" will be zero(0), or at least will not increment this count.