To improve the I/O performance

The subject of this month is IO. Here is my quick blog post on how Cover Index can Improve Performance by Reducing IO.

Let us kick off this post with disclaimers about Index. Index is a very complex subject and should be exercised with experts. Too many indexes, and in particular, too many covering indexes can hamper the performance. Again, indexes are very important aspect of performance tuning. In this post, I am demonstrating very limited capacity of Index. We will create covering index for query and see how its performance improves as IO reduces. Please note that do not run this scripts on production server as they contain DBCC commands which can create performance issues. This script is created for demo purpose only.

Let us run the following query and see the DBCC outcome along with the enabling of actual execution plan. Estimated Execution plan may work but I have used actual execution plan in this demo.

USE [AdventureWorks]
GO
SET STATISTICS IO ON
GO
-- Clean the buffers
-- Do not run these DBCC on production server
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
-- Run Select Statement
SELECT ProductID, ProductNumber, Color
FROM Production.Product
WHERE ProductID < 500
GO

The result of the statistics is as follows.

Table 'Product'. Scan count 1, logical reads 6, physical reads 3, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The result of the execution plan mouseover displays many different information. Please pay attention to IO cost.

Now let us create covering index on the query which we have just ran. We will include all the columns from the select statement in our newly created index.

-- 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

Now as cover index is created, let us run the above query once again. I will keep the actual execution plan and Statistics IO on.

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

The above query will pick up the newly created index right away. If due to any reason, it does not use our index for demo purpose, you can use hint to use that index. Let us examine the results once the query runs.

Table 'Product'. Scan count 1, logical reads 3, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The result of the execution plan mouseover displays many different information. Please pay attention to IO cost.

Let us compare the statistics here first.

Before Cover Index: IO Cost 0.006088

logical reads 6, physical reads 3, read-ahead reads 16,

After Cover Index: IO Cost 0.0038657

logical reads 3, physical reads 1, read-ahead reads 0

Logical read is almost half and Physical reads are much reduced after the creation of  cover index. Additionally, read-ahead reads are reduced with big numbers as well. Now when comparing execution plans, the IO cost is reduced to almost half after creating the covering index.

It is quite clear from this demo that IO is reduced due to cover index.

Let us compare the performance of the both the queries here. I have forced the indexes on them to simulate their original situation.

-- 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

It is quite clear from this demo that IO is reduced due to cover index, and additionally, the performance improves.

Run following clean up code to drop newly created objects.
-- Clean up
SET STATISTICS IO OFF
GO
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