This blog post is in the response of the T-SQL Tuesday #004: IO by Mike
Walsh. 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]GOSET STATISTICS IO ONGO-- Clean the buffers-- Do not run these DBCC on production serverDBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSGO-- Run Select StatementSELECT ProductID, ProductNumber, ColorFROM Production.ProductWHERE ProductID < 500GO
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 IndexIF 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]GOCREATE 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 buffersDBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSGO-- Run the following Select-- Use the hint if the query does not use the index onlySELECT ProductID, ProductNumber, ColorFROM Production.Product --WITH (INDEX(AK_Product_Cover))WHERE ProductID < 500GO
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 PerformanceSELECT ProductID, ProductNumber, ColorFROM Production.Product WITH (INDEX(PK_Product_ProductID))WHERE ProductID < 500GOSELECT ProductID, ProductNumber, ColorFROM Production.Product WITH (INDEX(AK_Product_Cover))WHERE ProductID < 500GO
It is quite clear from this demo that IO is reduced due to cover
index, and additionally, the performance improves.
