How does SQL Server determine the order of key columns in its missing index recommendations for a query plan?

When SQL Server creates a missing index recommendation for a particular query plan, it separates possible key columns into 2 groups. The first set contains all of the recommended columns that are part of an EQUALITY predicate. The second set contains all of the recommended columns that are part of an INEQUALITY predicate.

Within each set, the columns are ordered by the ordinal position of the columns, based on the table definition.

(Many thanks to Brent Ozar for building a repro script against the Stack Overflow database to prove this!)

1. Create 3 identical tables, but put their columns in different order. (The reason here is to use a variety of column names and datatypes to show that that doesn't impact the column order in the missing index recommendation.)

CREATE TABLE dbo.NumberLetterDate (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
fINT INT, fNVARCHAR NVARCHAR(40), fDATE DATETIME, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.LetterDateNumber (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
fNVARCHAR NVARCHAR(40), fDATE DATETIME, fINT INT, AboutMe NVARCHAR(MAX));
GO
CREATE TABLE dbo.DateNumberLetter (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
fDATE DATETIME, fINT INT, fNVARCHAR NVARCHAR(40), AboutMe NVARCHAR(MAX));
GO

2. Populate the tables with the same data. Get 100,000 rows from the Users table with real-world data distribution.

INSERT INTO dbo.NumberLetterDate(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.LetterDateNumber(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO
INSERT INTO dbo.DateNumberLetter(fINT, fNVARCHAR, fDATE, AboutMe)
SELECT TOP 100000 Age, DisplayName, LastAccessDate, AboutMe
  FROM dbo.Users WITH (NOLOCK)
  ORDER BY Id;
GO

3. Write a query that needs an index. Start with 3 equality filters, filtering for an exact value in all 3 fields. Note that all 3 queries have the same fields in the same order:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT = 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

All three tables have exactly the same data, and the queries are identical. The only difference is the field order - and that's the difference in our missing index requests, too:

Execution plans with 3 equality fields

In the execution plans, the column order in the missing index request exactly matches the column order in the table. For example, in dbo.NumberLetterDate, the number column is first, so it's first in the missing index request, too:

  • On dbo.NumberLetterDate, the missing index is on fINT (number), fLetter (nvarchar), fDate, the same order of the fields in the table
  • On dbo.LetterDateNumber, the index order switches to fNVARCHAR, fDATE, fINT
  • On dbo.DateNumberLetter, the index order switches to fDATE, fINT, fNVARCHAR

For a single-table operation like this, the index field order doesn't appear to depend on selectivity, datatype, or position in the query. (I leave it to other folks to prove this with more complex queries & joins.)

4. Mix in an inequality filter. On the INT field, for example, put in <> 100 as the filter:

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR = 'Brent Ozar'
  AND fDATE = '2018/01/01'
  AND 1 = (SELECT 1);
GO

In the execution plans, equality fields go first, then inequality fields - so here, fINT shows up last in all 3 missing index requests because it's an inequality search:

Execution plans with 2 equality, and 1 inequality search

5. Use 3 inequality filters. Use the same search for all fields (<>):

SELECT ID
  FROM dbo.NumberLetterDate
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.LetterDateNumber
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);

SELECT ID
  FROM dbo.DateNumberLetter
  WHERE fINT <> 100
  AND fNVARCHAR <> 'Brent Ozar'
  AND fDATE <> '2018/01/01'
  AND 1 = (SELECT 1);
GO

Since there's no equality searches, all 3 fields have the same priority order in the missing index recommendation, and now we're back to sorting purely by field order:

Execution plans with 3 inequality searches

  • 7
    That is much more simplistic than I ever would have imagined. – Aaron Bertrand Jun 6 at 23:56
  • I used to have my own theory about this subject, but it would to work only 80% of the time. Now I know why! Thanks for that – Dan Jun 11 at 14:42
  • "ordinal position of the columns, based on the table definition" makes some intuitive sense for us amateurs who always put the primary key and most important fields (the ones we thought of before we realized what else might be needed) first in the definition. – mickeyf Jun 11 at 18:52
  • Another thing that matters is the ordinal position of the index itself in other words whatever you’re leading column is. There was some work I was told back in 2012 to get around this but still the optimizer will miss an index for example if it’s a compound index with three columns and the second column is what you’re using in your where clause chances are the optimizers is not going to hit it ordinal position matters many places. – Tony Lucero Sep 6 at 11:34

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.