SQL SERVER – Improve Performance by Reducing IO – Creating Covered Index

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 (http://blog.SQLAuthority.com)

About these ads

33 thoughts on “SQL SERVER – Improve Performance by Reducing IO – Creating Covered Index

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

    Like

    • Hi Paresh,

      Thanks for kind words. The complete script is given in the post. You can recreate any scenario and test it out yourself.

      I have one more post in future coming out which is comparing regular index with included column. Stay tune for the same.

      Kind Regards,
      Pinal

      Like

      • Hello Pinal,

        Thanks for reply.

        Yes, I can not create all index at a time on one column .

        Please post the next article by considering either of them with comperision of the regular, covered and index with included columns with each other and their performance on execution plan.
        so we can learn more from that.

        Thank you again.

        Like

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

    Like

  3. 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?

    Like

    • 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 :)

      Like

      • Marko,

        This is excellent Point. Having Lob in your covering index changes everything. I am in fact working on very similar example on my test environment. Where I am trying out with Included index with Lob. That is the post I am working on but yet, I have not find good test output, which I can put in blog.

        I am working on this examples and will make blog post and post soon. In fact I put disclaimer first (which I usually put at the end) because of LOB and Included concept.

        I like how you and other readers are taking interest and participating. I am interested to see your test environment results too.

        Kind Regards,
        Pinal

        Like

        • Here’s some test results. I couldn’t test on our biggest test server though but I think the one I used isn’t not too bad either.

          Also I didn’t have much time because I’m working on this project I emailed you about (and it’s not coming very good, we probably have to redesign major parts of the DB). This is why my tests are not as comprehensive as I would like them to be. I think I see more into this at weekend.

          I created two tables now, one with non-LOB field varchar(4000) and one with LOB field varbinary(max). Then I basically ran the same tests as before with my laptop.

          And here are the results. First the table without LOB without covering index, then table without LOB with covering index, then table with LOB without covering index and last with LOB with covering index.

          Table ‘TestTable1′. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
          CPU time = 0 ms, elapsed time = 2 ms.

          Table ‘TestTable1′. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
          CPU time = 0 ms, elapsed time = 3 ms.

          Table ‘TestTable2′. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 890, lob physical reads 0, lob read-ahead reads 0.
          CPU time = 0 ms, elapsed time = 277 ms.

          Table ‘TestTable2′. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 892, lob physical reads 0, lob read-ahead reads 0.
          CPU time = 0 ms, elapsed time = 249 ms.

          Like

  4. 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?

    Like

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

    Like

    • Thank you very much Mike,

      It was my pleasure to be part of TSQLTuesday hosted by you. IT was really fun and along the way I learn something new.

      Many thanks for kind words. I will sure send you email.

      Kind Regards,
      Pinal

      Like

    • John,

      As a avid follower of your blog, I enjoy reading your blog and frequent updates on twitter.

      I have been corrected many times. I believe it is better to accept that I do not know and learn rather than just keep myself in corner without learning.

      Kind Regards,
      Pinal

      Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Data Warehouseing Concepts – Day 20 of 31 Journey to SQLAuthority

  7. Hi Pinal
    I have a 45GB database(Sql server 2008), How to increase the performance of the databse & server.
    Please Suggest me ASAP.

    Like

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #019 | SQL Server Journey with SQL Authority

  9. 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?

    Like

  10. 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!!!

    Like

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

    Like

    • Hii Pinal, How can i increase the performance of query having multiple like clauses starting and ending with wild card operator %.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s