SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Covering Indexes: Not Just for SELECT but also for UPDATE statements

By Mike Byrd,

SQL Server covering indexes have gain much attention for performance tuning for SELECT statements, but little has been said on their effect on the UPDATE statement. This paper will discuss these factors and show potential performance enhancements for specific instances.

The scripts below show performance for a covering index for a SELECT statement using the AdventureWorks2017 database and then extend that index to show potential performance benefits for UPDATE statements as well.

Consider the following T-SQL statement:

  USE AdventureWorks2017
  GO
  SET STATISTICS IO,TIME ON  --poor man's profiler; I use all the time for performance tuning
  GO
  SELECT SalesOrderID, AccountNumber, CustomerID
         FROM Sales.SalesOrderHeader
         WHERE SalesPersonID = 277
         ORDER BY 1
  GO
  SET STATISTICS IO,TIME OFF
  GO
  --     (473 row(s) affected)
  --     query cost = 0.545; 689 logical reads

And yields a query plan:

Figure 1: Clustered Index Scan (© 2018 | ByrdNest Consulting)

Note the clustered index scan with a total query cost of 0.545. The original index, IX_SalesOrderHeader_SalesPersonID, is based on a single column of SalesPersonID. If we run the same query with a different SalesPersonID:

  SET STATISTICS IO,TIME ON
  GO
  SELECT SalesOrderID, AccountNumber, CustomerID --note index seek (IX_SalesOrderHeader_SalesPersonID), 
         FROM Sales.SalesOrderHeader                    --       but resulting Key Lookup
         WHERE SalesPersonID = 285
         ORDER BY 1
  GO
  SET STATISTICS IO,TIME OFF
  GO
  --(16 row(s) affected)
  --query cost = 0.052; 50 logical reads

With a Query plan of

Figure 2: Key Lookup (© 2018 | ByrdNest Consulting)

Strange; same query but different query plans. The reason is primarily the size (number of rows) in the result sets. The first query returned 473 rows and the second query returned 16 rows. In the first query, the compiler decided (from the statistics) that it was better to do a clustered index scan because of the larger result set.

In the second query, the compiler (again, from statistics) decided that an index seek on SalesPersonID with a resulting lookup on the clustered index was better performing. This is a prime example of what might happen in a stored procedure with parameter sniffing – only in a stored procedure the query plan (whichever it might be) sticks around for a while.

But, let’s get back to covering indexes. If we go back and add the key lookup output parameters with an INCLUDE clause we can now get a single index that includes all the columns needed for the query.

  CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_SalesPersonID ON Sales.SalesOrderHeader
         ([SalesPersonID] ASC)
         INCLUDE (AccountNumber, CustomerID)
         WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = ROW)      
  GO

The column SalesOrderID (PK) is not needed since it is automatically added to the index. So, now running the first SELECT we get:

  SET STATISTICS IO,TIME ON
  GO
  SELECT SalesOrderID, AccountNumber, CustomerID --now index seek (because the index is "covering")
         FROM Sales.SalesOrderHeader
         WHERE SalesPersonID = 277
         ORDER BY 1
  GO
  SET STATISTICS IO,TIME OFF
  GO
  --(473 row(s) affected)
  --query cost = 0.005; 5 logical reads

with a query plan of

Figure 3: Index Seek (© 2018 | ByrdNest Consulting)

Wow, quite an improvement: 689 logical page reads down to 5 and query cost from 0.545 down to 0.005.

Rerunning Query 2 we get

  SET STATISTICS IO,TIME ON
  GO
  SELECT SalesOrderID, AccountNumber, CustomerID
         FROM Sales.SalesOrderHeader
         WHERE SalesPersonID = 285
         ORDER BY 1
  GO
  SET STATISTICS IO,TIME OFF
  GO
  --(16 row(s) affected)
  --query cost = 0.005; 3 logical reads

with the same query plan as above. Note the query dropped from 50 logical page reads down to 3. This is a great example of how a covering index can greatly benefit a SELECT query. Note both queries used the same query plan.

There are many WOMs (word of mouth) about indexes and their utilization, but sufficed to say you should still keep indexes (especially in OLTP systems) narrow in width. You don’t want to create a covering index that essentially reorders the clustered index (whole table).

So, we now have a covering index based on SalesPersonID. Let’s now look at a similar (in logic) T-SQL UPDATE statement:

  UPDATE soh
         SET AccountNumber = AccountNumber + '*',
               RevisionNumber = RevisionNumber + 1,
               ModifiedDate  = GETDATE()
         FROM Sales.SalesOrderHeader soh
         JOIN Sales.SalesPerson sp
           ON sp.BusinessEntityID = soh.SalesPersonID
         WHERE sp.TerritoryID = 1;
  GO
  --Clustered Index scan on IX_SalesOrderHeader_SalesPersonID  (60% of query plan)
  --     (Output = SalesOrderID, RevisionNumber, AccountNumber, SalesPersonID)
  --Total Query cost = 9.01

with an estimated query plan of

Figure 4: QueryPlan Clustered Index (© 2018 | ByrdNest Consulting)

where the numbers in green (above) come from the estimated query plan. Note that there are Clustered Index scans on both tables and that the scan on SalesOrderHeader is 60% of the query plan cost.

This update statement has 2 additional columns not in the covering index: RevisionNumber and ModifiedDate. If we revise the covering index as

  CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_SalesPersonID ON Sales.SalesOrderHeader
         ([SalesPersonID] ASC)
         INCLUDE (AccountNumber, CustomerID, RevisionNumber, ModifiedDate)                     
         WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = ROW)           
  GO

And rerun the same UPDATE statement above we get an index seek: IX_SalesOrderHeader_SalesPersonID, with a query cost = 6%, and an overall query plan cost = 0.146. The estimated query plan is:

Figure 5: QueryPlan Index Seek (© 2018 | ByrdNest Consulting)

Now there is an index seek on Sales.SalesOrderHeader with the seek only 6% on the total query plan and the total query plan cost dropping from 9.01 to 0.146. Now let’s look at another UPDATE, but first revert back to the original covering index:

  CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_SalesPersonID ON Sales.SalesOrderHeader
         ([SalesPersonID] ASC)
         INCLUDE (AccountNumber, CustomerID)
         WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = ROW)
  GO

This second UPDATE statement is still on Sales.SalesOrderHeader:

  UPDATE soh
         SET AccountNumber = AccountNumber + '*',
               RevisionNumber = RevisionNumber + 1,
               ModifiedDate  = GETDATE()
         FROM Sales.SalesOrderHeader soh
         WHERE SalesPersonID in (275,280,285,290)
  GO
  --now we get a clustered index scan on PK_SalesOrderHeader with Query Plan = 74%
  --Overall query plan cost is 0.731

and with the original covering index has an estimated query plan

Figure 6: QueryPlan Update Clustered Index Scan (© 2018 | ByrdNest Consulting)

This time it is asking for a revised index with INCLUDE (RevisionNumber,AccountNumber). If we apply the same covering index as we did for the first update:

  CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_SalesPersonID ON Sales.SalesOrderHeader
         ([SalesPersonID] ASC)
         INCLUDE (AccountNumber, CustomerID,RevisionNumber, ModifiedDate)                      
         WITH (DROP_EXISTING = ON, ONLINE = ON, DATA_COMPRESSION = ROW)           
  GO

And rerun the 2nd UPDATE query we get a NonClustered Index seek on IX_SalesOrderHeader_SalesPersonID (query cost 4%) and an overall query plan cost is 0.149

Figure 7: QueryPlan Update Index Seek (© 2018 | ByrdNest Consulting)

where we see an overall query plan cost drop from 0.731 to 0.149 and the clustered index scan taking 74% of the query cost dropping to an index seek costing only 4% of the overall query cost for the Sales.SalesOrderHeader table

Conclusions

So now we have a covering index that improves performance for both SELECT and Update statements.

Generally covering indexes not only help SELECT query performance, but as shown can help UPDATE performance. Although not shown here, I ran into a client scenario using the KEY lookup (as described in the SELECT example above) for an UPDATE statement that was having deadlock issues due to a concurrency as well as a double lookup on the applicable table. Adding a covering index resolved that problem also. In any case, as Microsoft always says: “Test, Test, Test!”

 
Total article views: 1308 | Views in the last 30 days: 11
 
Related Articles
ARTICLE

Covering Index using Included Columns

This article from Josef Richberg details the benefits of included columns for use in creating a cove...

BLOG

Covering Indexes

Microsoft continues to improve indexes and options for additional performance enhancements. One I se...

ARTICLE

Interviews: Covering index

A few notes on how to prepare for an interview with indexing questions.

FORUM

Updating Covering Indexes

Hi, During some performance tuning and optimization I performed at a client, I discovered what ap...

BLOG

Find Duplicate (and covered) Indexes

A while back, I wrote a simple little query that lets you Get Index Included Column Info (because sp...

Tags
 
Contribute