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
