Greetings SQL Server enthusiasts! In this blog post, we will be discussing the concept of cover index and how it can significantly improve performance by reducing IO.

Before we dive into the details, it’s important to note that index optimization is a complex subject and should be approached with caution. Having too many indexes, especially covering indexes, can actually hinder performance. However, when used correctly, indexes play a crucial role in performance tuning.

Let’s start by running a query and examining the outcome using the DBCC command and the execution plan:

USE [AdventureWorks]
GO

SET STATISTICS IO ON

-- Clean the buffers (do not run these DBCC commands on production server)
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

-- Run the Select Statement
SELECT ProductID, ProductNumber, Color
FROM Production.Product
WHERE ProductID < 500
GO

The result of the statistics shows the number of logical reads, physical reads, and read-ahead reads performed by the query. Additionally, the execution plan provides valuable information, particularly the IO cost.

Now, let’s create a covering index for the query we just ran. A covering index includes all the columns from the select statement:

-- Create Covering Index
IF EXISTS (SELECT * FROM sys.indexes WHERE OBJECT_ID = OBJECT_ID(N'[Production].[Product]') AND name = N'AK_Product_Cover')
    DROP INDEX [AK_Product_Cover] ON [Production].[Product]
GO

CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_Cover] ON [Production].[Product] (ProductID, ProductNumber, Color) ON [PRIMARY]
GO

With the cover index in place, let’s rerun the query and observe the results:

-- Clean the buffers
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

-- Run the Select
SELECT ProductID, ProductNumber, Color
FROM Production.Product
-- WITH (INDEX(AK_Product_Cover)) -- Use this hint if the query does not use the index only
WHERE ProductID < 500
GO

As you can see, the query now utilizes the newly created cover index. The statistics show a significant reduction in logical reads, physical reads, and read-ahead reads. The execution plan also reflects the improved IO cost.

Comparing the performance of the two queries, one using the primary key index and the other using the cover index, it becomes evident that the cover index not only reduces IO but also improves overall query performance:

-- Comparing Performance
SELECT ProductID, ProductNumber, Color
FROM Production.Product WITH (INDEX(PK_Product_ProductID))
WHERE ProductID < 500
GO

SELECT ProductID, ProductNumber, Color
FROM Production.Product WITH (INDEX(AK_Product_Cover))
WHERE ProductID < 500
GO

In conclusion, the cover index significantly improves query performance by reducing IO. It is important to note that this is just a demonstration of the concept and should not be implemented on a production server without proper analysis and testing.

Stay tuned for future posts where we will explore other performance optimization techniques, such as included indexes.

Thank you for reading and happy coding!