SQLMentoring

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 UPDATE statement.  This paper will discuss these factors and show potential performance enhancements for specific instances.

The scripts below will 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 TSQL 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
 

 Type your paragraph here.