Missing Index Recommendation Column Order

When SQL Server is processing a query, it will sometimes make a suggestion for an index that it believes will help that query run faster. These are known as missing indexes, which can be obtained from the missing index DMVs, or more directly by showing the graphical execution plan when you execute the query in SSMS. But can you blindly create the missing index that recommended by SQL Server? In this post, I will show you why you cannot blind trust what SQL Server told you to do.

CREATE TABLE Candidates (

id int PRIMARY KEY IDENTITY(1, 1),

gender char(1) NOT NULL,

displayName varchar(10) NOT NULL

);

GO

SET NOCOUNT ON;

INSERT Candidates (gender, displayName) SELECT 'M', substring(replace(newID(), '-', ''), cast(RAND() * (31 - 10) AS INT), 10);

GO 500000

INSERT Candidates (gender, displayName) SELECT 'F', substring(replace(newID(), '-', ''), cast(RAND() * (31 - 10) AS INT), 10);

GO 500000

UPDATE STATISTICS Candidates WITH FULLSCAN;

GO

-- Enable Actual Execution Plan for below query

SELECT displayName, gender FROM Candidates WHERE displayName = 'CF53A906FD' AND gender = 'M' OPTION (RECOMPILE);

And here is the recommended index to create:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Candidates] ([gender],[displayName]);

As you can see, the column order has no any considerations on selectivity, they are just a comma-delimited list of column order in the table (actually the key columns in the recommended index will be ordered by equality predicate group first, then inequality predicate group, and inside each group follows the column order in table definition). You can double check the column order in the table definition by executing sp_help system stored procedure.