When developing indexes to support queries it's important to pay attention to order of the key columns in the index key list. An important factor for determining key order is the type of operator used in the predicate e.g. an equality or range operator. When the predicates are strictly equality then key ordering is not as important. For example, Take the following query ran against the WorldWideImporters demo database
/* //////////////// 1) Create Indexes///////////////////////////// */
--Create Index with Order Date Key First
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ix_OD_SI_NonClus')
DROP INDEX ix_OD_SI_NonClus ON Fact.[Order]
CREATE NONCLUSTERED INDEX ix_OD_SI_NonClus
ON [WideWorldImportersDW].[Fact].[Order] ([Order Date Key],[Stock Item Key])
INCLUDE ([Quantity],[Unit Price],[Order Key])
--Create Index with Stock item Key First
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'ix_SI_OD_NonClus')
DROP INDEX ix_SI_OD_NonClus ON Fact.[Order]
CREATE NONCLUSTERED INDEX ix_SI_OD_NonClus
ON [WideWorldImportersDW].[Fact].[Order] ([Stock Item Key],[Order Date Key])
INCLUDE ([Quantity],[Unit Price],[Order Key])
/* //////////////// 2) Prep Cache ///////////////////////////// */
CHECKPOINT --Writes dirty buffers to disk
DBCC DROPCLEANBUFFERS --Clears contents of buffers, Don't run in production without consent
/* //////////////// 3) Test Queries ///////////////////////////// */
Go
SET STATISTICS IO, TIME ON
SELECT [Order Key]
,[Stock Item Key]
,[Order Date Key]
,[Quantity]
,[Unit Price]
FROM [WideWorldImportersDW].[Fact].[Order] WITH (INDEX(ix_OD_SI_NonClus))
WHERE [Stock Item Key] = 198
AND [Order Date Key] = '2013-01-07'
SELECT [Order Key]
,[Stock Item Key]
,[Order Date Key]
,[Quantity]
,[Unit Price]
FROM [WideWorldImportersDW].[Fact].[Order] WITH (INDEX(ix_SI_OD_NonClus))
WHERE [Stock Item Key] = 198
AND [Order Date Key] = '2013-01-07'
The execution cost for both these is index is split 50/50. Also both indexes have the exact amount of IO and minor variations in CPU time.
Some site selectivity as an important factor for determining key order because the optimizer creates statistics for the first index column. Yes, the optimizer does create a histogram on the leading column to start off with but if SQL server needs statistics for another column it will build them.
Range Predicates
When queries contains a mixture of equality and range predicates (typical for date related queries) then make create the index with the equality predicate columns first and the range predicate columns last.
The index with Stock Item column leading performs better when we change Order Date Key predicate from equality to a range.
The equality predicate first index performs better because the storage engine seeks to leaf level of the index, finding the first record that satisfies the condition "[Stock Item Key] = 198", Then it just needs to perform a range scan on the rows until it has found every row meeting the condition [Order Date Key] > '2013-01-07'. When the range predicate is listed first then SQL server scans all the rows for the date predicate then as a residual it looks for rows satisfying the condition "[Stock Item Key] = 198". IO and CPU stats show how the storage engine cannot traverse the Index as optimally when [Order Date Key] is listed first.
In conclusion, when we have only equality predicates then index key order is usually trivial. When ranges are introduced then list those columns last. Be sure to test all index changes against workloads for performance before releasing to production.
Comments