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.

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from jasper22's posterous