在SQL Server中使用Hints测试索引（Using Hints To Test Indexes in SQL Server）

最新推荐文章于 2024-11-15 16:13:39 发布

武哥博文

最新推荐文章于 2024-11-15 16:13:39 发布

阅读量897

点赞数

分类专栏： 001-C 数据库性能 001-A 数据库开发文章标签： sql server 测试 include query returning performance

001-A 数据库开发同时被 2 个专栏收录

146 篇文章 0 订阅

订阅专栏

001-C 数据库性能

65 篇文章 0 订阅

订阅专栏

from http://www.mssqltips.com/tip.asp?tip=2045

Using Hints To Test Indexes in SQL Server
Written By: Ken Simmons -- 6/24/2010 -- 1 comments -- print -- free stuff --

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'

As you can see by the following execution plan, 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)

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?

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'

SELECT

NationalIDNumber
FROM HumanResources.Employee
WHERE Title = 'Stocker'

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.