Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Hints To Test SQL Server Indexes


By:   |   Read Comments (1)   |   Related Tips: More > Indexing

Attend these FREE MSSQLTips webcasts >> click to register


Problem

When tuning indexes, it is somewhat difficult to tell how much impact the changes you have made will have on a query. Other than just looking at the different execution plans, is there an easy way to compare the queries using the old and new indexes? 

Solution

After making index changes, I always like to use hints to see how much impact the change has on a query. Let's take the following query that uses the AdventureWorks database as an example.

USE AdventureWorks
GO
SELECT
NationalIDNumber
FROM HumanResources.Employee
WHERE Title = 'Stocker'
GO

As you can see by the following execution plan, the query uses a clustered index scan to retrieve the information.

 the query uses a clustered index scan to retrieve the information

Since the query is searching by Title and returning the NationalIDNumber, you can create the following non-clustered index on Title and include the NationalIDNumber, so all of the information the query needs is located in the index. You should note that if you did not include the NationalIDNumber, the optimizer would still find it more optimal to scan the clustered index instead of seeking the non-clustered index and having to lookup the NationalIDNumber.

CREATE INDEX ix_Title_Include ON HumanResources.Employee(Title) 
INCLUDE (NationalIDNumber)
GO

After creating the new index and rerunning the original query, you will now see that the query is performing an index seek on the new non-clustered index. That sounds good, but how much more efficient is it?

you will now see that the query is performing an index seek on the new non-clustered index

In order to answer that question, you can use a hint to force one of the queries to use the original index as shown in the following query.

SELECT NationalIDNumber
FROM HumanResources.Employee 
WITH (INDEX (PK_Employee_EmployeeID))
WHERE Title = 'Stocker'
GO
SELECT NationalIDNumber
FROM HumanResources.Employee
WHERE Title = 'Stocker'
GO

You can now see that using the new index, the query cost is only 29% compared to the original index which is 71%. If you saw something like 50/50, you may want to rethink the new index, because it hasn't given you any performance and now you have an additional index to update and maintain.

 the query cost is only 29% compared to the original index which is 71%

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ken Simmons Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 24, 2010 - 7:21:30 AM - Ozzie Back To Top

 That was a really good tip - it is hard to do side by side comparisions - that makes it simple.

 

Doug


Learn more about SQL Server tools