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]
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
Well, summary is very simple that because of cover index, the performance of the query improves and IO decreases. Included index is another good concept, we will talk about it in later post.
Reference : Pinal Dave (https://blog.sqlauthority.com)
31 Comments. Leave new
Hi Pinal,
Nice article for the covered index.
But have one question,
The same example given you for the table, If there are the three indexes
1. Nonclusterd index on ProductID column only.
2. Nonclusterd index on column these covered three columns (ProductID, ProductNumber, Color) which you created in example.
3. Nonclusterd index on ProductID column with included column (ProductID, ProductNumber, Color).
At that time which index is used at runtime ?.
Which index we should be best and need to keep and which need to drop for best execution?.
Can these all there index degrade performance?
Can you please suggest?
Thanks.
Great Article!
It is so easy to understand. Hats off!
Nice.
Nice.
I meant to say, where did you get this ideas like this.
Keep it up!
Great article pinal, as you rightly pointed indexes are a tricky area to be in, in fact during the sql saturday event in charlotte,NC there was a overwhelming stress on the need to test different scenarios using indexes before choosing the right one. The quantity of data for testing the effectiveness of index should mimic production data as much as possible.
Do you think that such a small table can demonstrate anything related to performance at all?
Unfortunately there’s also downside in this, as there is with everything.
1. It’s a trade-off with updates, inserts and deleted. Same thing with non-covering indexes also.
2. This could actually increase I/O cost if you add too many key or non-key columns. This is because of cache misses.
3. Disk space requirements grows. Especially if used with varchar(max), nvarchar(max), varbinary(max) or xml data types.
So DBA needs to think carefully and measure performance with and without the index(es) and make judgement base on that. Well, you should be doing performance testing in any case ;)
By the way. I stumbled into fascinating subject about index sorting when scouring throught the MSDN. It seems that you can redirect index creation to TEMPDB in order to speed up the process. Check out the option SORT_IN_TEMPDB in MSDN.
Btw2. Pinal. If you don’t mind I would like to send you a very tricky (well, tricky to me) query which is running too slow and I can’t come up with anything to speed it up. If that’s okay with you?
I went and tested this a bit. First I created a table:
CREATE TABLE [dbo].[LargeObjectTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[data] [varbinary](max) NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
)
Then I added couple of rows to it. I used some 300 megabyte binary file as data. I used following select statement in tests:
SELECT * FROM LargeObjectTable WHERE id < 4
First below is statistics without covering index. After that with covering index CREATE INDEX IX_foo ON LargeObjectTable(id) INCLUDE(data)
Table 'LargeObjectTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 412629, lob physical reads 2648, lob read-ahead reads 119884.
CPU time = 1201 ms, elapsed time = 13638 ms.
Table 'LargeObjectTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 412629, lob physical reads 6445, lob read-ahead reads 119832.
CPU time = 1342 ms, elapsed time = 19362 ms.
So LOG page reads were done almost 2,5 times more with covering index.
Now this test was executed on my laptop so I wouldn't read too much into this. I'll try something little more comprehensive with our big test servers at work tomorrow.
I must say I really love making tests like this :)
This is a horrible example of using covering indexes. Yes, you did slightly reduce your IO but you already had an index that was a very good match for your query. On a large table the overhead you add with your new index will almost certainly outweigh the minor boost to performance on your reads.
The danger is that many of your inexperienced readers will take your advice out of ignorance and start throwing covering indexes around willy nilly where they are not needed.
With so many good examples that you could create, why choose such a bad one?
Hey Pinal!
Thanks for participating in this month’s T-SQL Tuesday. Great post, as always. I hope you, your wife and that cute baby (More pictures would be a fine subject for a blog post, or at least an e-mail :) :) ) are doing well.
I would have thought the clustered index seek would have been better. I guess I still have a lot to learn. As always another great post.
very good and delicious article.
THe article test like vadilal ice cream.
Hi Pinal
I have a 45GB database(Sql server 2008), How to increase the performance of the databse & server.
Please Suggest me ASAP.
Nice Post.. Glad to learn this concept..
Good one .. i loved it..
Nice tutorial pinaldave you are great!! bhaqwan aapko khush rakhay
Hello Sir,
If the given query will good for particular table or some cause but i wants to handle entire database.How it will help me?
Pinal,
I have started reading on Indexes and I have a doubt regarding the query.
ProductId is a Primary key as well as an Indentity key for the table Production.Product.
So, it eventually becomes a Clustered Index.
If advisable to have a custered key also included in the Non Clustered -Covering Index?
Thanks!!!
Hi pinal your posts are awesome and very explanatory.I dont have much experience but do have question? what is the difference between creating non clustered index on multiple columns and covering index? are they same.bit confused
Hii Pinal, How can i increase the performance of query having multiple like clauses starting and ending with wild card operator %.