This is scholastic scenario build from a production issue I encountered not too long ago on a client database.
In the client database, a covered index was defined with main objective maximize the performance between an UPDATE and a SELECT query. The DBA created the covered index that contains columns in the SELECT query and in the WHERE clause, and then observed an Index Seek in the execution plan. During peak activity usage, the DBA still observe slight blockings and numerous deadlocks.
The focus is on SQL Server behaviour and assumes I/O and memory are optimal.
Setting up the SQL database
I am running SQL 2012 SP1 using AdventureWorks2012 database. You can download the AdventureWorks2012 database from http://msftdbprodsamples.codeplex.com/releases/view/55330 > AdventureWorks2012-Full Database Backup.zip
A link is provided at the bottom of this article to download the script used.
In order to understand the solution, the steps below will guide you to reproduce and learn the SQL Server behaviour highlighted in this article.
1. Create a new table and covered index
Instead of meddling with an existing table, let's use a temp table instead. We’ll be creating a composite index for column ModifiedDate, CarrierTrackingNumber and ProductID.
The table to be used is Sales.SalesOrderDetail as below
USE AdventureWorks2012 go SELECT * INTO #Sales FROM Sales.SalesOrderDetail go CREATE NONCLUSTERED INDEX NCI_Sales_Demo on #Sales(ModifiedDate, CarrierTrackingNumber, ProductID) go
2. Turn On the Include Actual Execution Plan option
The query below will use the composite index created as covered index. The variable declaration and the column data type is the same. Execute the query and review the execution plan
DECLARE @ModifiedDate DATETIME DECLARE @ProductID INT DECLARE @Carrier NVARCHAR(50) SELECT ModifiedDate, CarrierTrackingNumber, ProductID FROM #Sales WHERE ModifiedDate = @ModifiedDate AND CarrierTrackingNumber = ISNULL(@Carrier, CarrierTrackingNumber) AND ProductID = @ProductID Go
Well, so far everything looks good. An Index Seek is used and there are no key lookups as the index “covers” the query columns. For various reasons, you should NOT stop here without looking further into your query details. This is an oversight that can catch you out later down the track.
If you hover over the Index Seek node, you will notice I have highlighted 2 boxes, 1 in red and 1 in black.
Figure 1: Index Seek Tooltip
The black box shows the predicate columns used for Index Seek, but pay attention to the red box because this indicate predicate columns that are unable to use the covered index efficiently.
Let's compare the same query but with parameter values and some tweak to better explain the scenario. Many approaches can be used to demonstrate the scenario, I choose to use statistics I/O to explain the behaviour.
SET STATISTICS IO ON DECLARE @ModifiedDate DATETIME = '1 Aug 2007' DECLARE @ProductID INT = 712 DECLARE @Carrier NVARCHAR(50) = 'FF50-4EB1-9E' SELECT ModifiedDate, CarrierTrackingNumber, ProductID FROM #Sales WHERE ModifiedDate = @ModifiedDate AND CarrierTrackingNumber = ISNULL(@Carrier, CarrierTrackingNumber) AND ProductID = @ProductID go
Running the query above returns 1 row, with I/O statistics output as below
Table '#Sales______________________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Now let's re-run the query again with just the ModifiedDate predicate. It returns 3,876 rows, but look at the statistics I/O again. It has exactly the same count as when all predicates were used.
SET STATISTICS IO ON DECLARE @ModifiedDate DATETIME = '1 Aug 2007' DECLARE @ProductID INT = 712 DECLARE @Carrier NVARCHAR(50) = 'FF50-4EB1-9E' SELECT ModifiedDate, CarrierTrackingNumber, ProductID FROM #Sales WHERE ModifiedDate = @ModifiedDate go
Table '#Sales______________________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
The covered index is used but it seems only column ModifiedDate in the covered index is utilized to provide the result.
This isn't a SQL bug. To better explain the behaviour, an Index Seek on a Composite Index can provide 2 main types of predicate – Predicate and Seek Predicate, or just Seek Predicate. An Index Seek on a single column only produces Seek Predicate. Referring to Figure 1: Index Seek Tooltip, we got both Predicate and Seek Predicate.
A Seek Predicate means the column is SARGable and the index is used effectively to filter results. Predicate means the column is non-SARGable and usually more rows are gathered by SQL, each rows tested and those passes are returned as results.
In this scenario, the first column ModifiedDate was able to use Seek to filter the results, and every row from this result is subsequently tested and filtered on the CarrierTrackingNumber and the ProductID predicate to produce the final result.
The ISNULL function on CarrierTrackingNumber column is preventing SQL from performing a Seek. Since the second column in Composite Index is unable to use Seek, the third columns is also rendered unusable since all 3 columns are specified in the WHERE clause.
Solutions
There are many other possible solutions to overcome the issue, below are 3 example solutions.
Example 1
A best case scenario would be to make all 3 columns SARGable. This normally requires a change in the query to remove the condition which prevents Seek Predicate in Index Seek. In our case, removing ISNULL function would allow Seek for all 3 columns.
DECLARE @ModifiedDate DATETIME = '1 Aug 2007' DECLARE @ProductID INT = 712 DECLARE @Carrier NVARCHAR(50) = 'FF50-4EB1-9E' SELECT ModifiedDate, CarrierTrackingNumber, ProductID FROM #Sales WHERE ModifiedDate = @ModifiedDate AND CarrierTrackingNumber = @Carrier AND ProductID = @ProductID go
Table '#Sales______________________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Example 2
If for some reason you’re unable to change the query, another method would be to change the Composite Index column ordering. Since ProductID and ModifiedDate columns are SARGable, with the same principle, it would make sense to perform an Index Seek on ModifiedDate and ProductID column followed by a test on CarrierTrackingNumber.
DROP INDEX #Sales.NCI_Sales_Demo CREATE NONCLUSTERED INDEX NCI_Sales_Demo ON #Sales (ModifiedDate, ProductID, CarrierTrackingNumber) go
Table '#Sales______________________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Example 3
Further to the principle on Example 2, what if you make CarrierTrackingNumber an included column instead?
drop index #Sales.NCI_Sales_Demo CREATE NONCLUSTERED INDEX NCI_Sales_Demo ON #Sales(ModifiedDate, ProductID) INCLUDE (CarrierTrackingNumber) go
Table '#Sales______________________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
As expected, the logical read count is the same since in both cases the Index Seek will utilise ModifiedDate and ProductID column and CarrierTrackingNumber is still non-SARGable.
Figure 2: Index Seek Tooltip - SARGable on ProductID and ModifiedDate
Another question would be, does the column ordering in composite index matter since we know for sure now we'll definitely be using ModifiedDate and ProductID as the SARGable columns? Let's swap ModifiedDate andProductID column in the index definition, execute and compare the statistics I/O again.
DROP INDEX #Sales.NCI_Sales_Demo CREATE NONCLUSTERED INDEX NCI_Sales_Demo ON #Sales(ProductID, ModifiedDate, CarrierTrackingNumber) go DECLARE @ModifiedDate DATETIME = '1 Aug 2007' DECLARE @ProductID INT = 712 DECLARE @Carrier NVARCHAR(50) = 'FF50-4EB1-9E' SELECT ModifiedDate, CarrierTrackingNumber, ProductID FROM #Sales WHERE ModifiedDate = @ModifiedDate AND CarrierTrackingNumber = ISNULL(@Carrier, CarrierTrackingNumber) AND ProductID = @ProductID go
Table '#Sales______________________________________________________________________________________________________________00000000003C'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Are you wondering why SQL is able to pin-point the exact rows with the same logical read count? If you think about it carefully, albeit ModifiedDate column contains more duplicates as the leading column in the covered index, the next column ProductID in the index order is used to filter the result with both columns used as one set. So SQL is still essentially working with the same number of records and the same set of data pages in this scenario. This principle is similar to an Index Lookup.
Summary
In a composite index, SQL execution plan will show Index Seek as long as the first column in the index definition is SARGable. It does not mean all columns are also used effectively albeit they are contained in WHERE clause.
It is important to make sure all columns in covered indexes are SARGable to efficiently filter the number of records returned. Don't be easily misled by thinking Index Seek node in execution plan would automatically mean all columns in composite index are used efficiently. It would be prudent to check the execution plan of Index Seek tooltip to make sure the columns are indeed using Seek Predicate.
I hope the article provided a different perspective in SQL performance tuning. Thank you for reading and I hope this article has been interesting.
