SQL Server Technical Bulletin - The Index Tuning Wizard

View products that this article applies to.

In many cases, you can avoid having to call Microsoft SQL Server Support by using the Index Tuning Wizard. One such case occurred with a company that had just upgraded from SQL Server 7.0 to SQL Server 2000. In SQL Server 7.0, a particular query ran in about 2 seconds. During the upgrade, an index hint was removed from the query. After the upgrade, the query took about 10 to 15 minutes to run. Because the query took a long time, the front-end application was experiencing query time-out errors.

Back to the top

Identification

The following is the text of the query:

select count(*) as imgcount, a.status_cd, image_received_dttm 
from images_detail a,  status_types b, WORK_QUE C 
where a.status_cd = b.status_cd AND 
A.WRKQUE_CD = C.WRKQUE_CD AND 
a.wrkque_cd = '812' AND 
C.IMAGETYPE_cd = 'claim' and 
status_active_fg = 'Y' 
group by image_received_dttm, a.status_cd 
order by image_received_dttm, a.status_cd

The execution plan of the query was the following when the query ran for 10 to 15 minutes:

Note Some sections of the query execution plan have been removed for readability purposes.

|--Sort(ORDER BY:([a].[IMAGE_RECEIVED_DTTM] ASC, [a].[STATUS_CD] ASC))
       |--Nested Loops(Inner Join)
            |--Filter(WHERE:([C].[IMAGETYPE_CD]='CLAIM'))
            |    |--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([EIP_PROD].[dbo].[WORK_QUE] AS
		  [C])) | |--Index Seek(OBJECT:([EIP_PROD].[dbo].[WORK_QUE].[PK_WORK_QUE] AS [C]),
		  |--Compute Scalar(DEFINE:([Expr1005]=Convert([Expr1010]))) |--Hash
		  Match(Aggregate, HASH:([a].[IMAGE_RECEIVED_DTTM], [a].[STATUS_CD]), |--Hash
		  Match(Right Semi Join, HASH:([STATUS_TYPES].[STATUS_CD])=([a].[STATUS_CD]),
		  |--Clustered Index
		  Scan(OBJECT:([EIP_PROD].[dbo].[STATUS_TYPES].[PK__STATUS_TYPES__4D94879B]),
		  |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]), |--Hash Match(Inner
		  Join, HASH:([Bmk1000])=([Bmk1000]), | |--Index Seek(OBJECT:([EIP_PROD].[dbo].[IMAGES_DETAIL].[xf_wrkque_cd] AS [a]),
		  | |--Index Scan(OBJECT:([EIP_PROD].[dbo].[IMAGES_DETAIL].[XIF2IMAGES_DETAIL] AS
		  [a])) |--Index Scan(OBJECT:([EIP_PROD].[dbo].[IMAGES_DETAIL].[XIFIMAGE_RECEIVED_DTTM]
		  AS [a]))

When the query was run through the Index Tuning Wizard in Query Analyzer, the following two indexes were recommended:

Collapse this tableExpand this table

Table	Columns of Index	Index Type
images_detail	status_cd, images_received_dttm, wrkque_cd	Non-clustered
wrk_que	wrkque_cd	Clustered

Back to the top

Resolution

After the query created the two indexes that were recommended in the Index Tuning Wizard, the query ran in 2 seconds, and the query generated the following execution plan:

Note Some sections of the query execution plan have been removed for readability purposes.

  |--Compute Scalar(DEFINE:([Expr1003]=Convert([Expr1004])))
       |--Stream Aggregate(GROUP BY:([a].[IMAGE_RECEIVED_DTTM], [a].[STATUS_CD]) DEFINE:([Expr1004]=Count(*)))
            |--Sort(ORDER BY:([a].[IMAGE_RECEIVED_DTTM] ASC, [a].[STATUS_CD] ASC))
                 |--Nested Loops(Inner Join)
|--Clustered Index Seek(OBJECT:([mydb].[dbo].[WORK_QUE].[PK_WORK_QUE] AS [C]), |--Nested
		  Loops(Inner Join, OUTER REFERENCES:([b].[STATUS_CD]) WITH PREFETCH)
		  |--Clustered Index
		  Scan(OBJECT:([mydb].[dbo].[STATUS_TYPES].[PK__STATUS_TYPES__4D94879B] AS [b]),
		  |--Index Seek(OBJECT:([mydb].[dbo].[IMAGES_DETAIL].[IMAGES_DETAIL16] AS [a]),

This execution plan shows that the index that the ITW recommended on the images_detail table made only one Index Seek operation necessary on that table. In the "Identification" section, you can see that the index that was used for the images_detail table made two Index Scans and one Index Seek necessary. Therefore, the addition of the index that was recommended by ITW turns the query into a covered query for the images_detail table. This increases the performance of the query.

In a covered query, all the columns specified in the query are contained in one index.

Also, the recommended clustered index on the wrk_que table made the Bookmark Lookup operation unnecessary. When the query ran in about 10 to 15 minutes, an Index Seek operation was performed on the non-clustered index on the wrkque_cd column of the wrk_que table. After this Index Seek operation was completed, the Bookmark Lookup operation was used to retrieve the corresponding row from the table. After the 2 indexes were added, the query ran in 2 seconds, and a Clustered Index Seek operation was performed on the new clustered index on the wrkque_cd column of the wrk_que table. (The old non-clustered index was dropped.) This Clustered Index Seek operation made the Bookmark Lookup operation unnecessary because the Clustered Index Seek operation provided the corresponding row from the table.

A Bookmark Lookup operation is used to retrieve data from a table. If the table has no clustered index, the row ID is used to retrieve the row from the table. If you eliminate a Bookmark Lookup operation, you may notice some performance improvement. Performance improves because fewer disk reads may be required and because the table does not have to be navigated to retrieve a row. Instead, the particular index that is stored in memory can be used to retrieve the row from the table. The particular index is stored in memory because of its size.

Using the Index Tuning Wizard to help tune a particular workload is very important. Many cases have been solved by using the Index Tuning Wizard.

Note Because the Index Tuning Wizard does not execute any batch of queries in the workload during analysis, the wizard cannot provide recommendations for a batch that references temporary objects.

Back to the top

Running the Index Tuning Wizard

The Index Tuning Wizard can be run from the following locations:

SQL Server Enterprise Manager:
1. In the left pane, click a server.
2. On the Tools menu, click Wizards, and then click Management. The Index Tuning Wizard is one of the wizards in the Management group.
SQL Profiler: On the Tools menu, click Index Tuning Wizard.
Query Analyzer: Select one Transact-SQL statement in the Query Analyzer query window. On the Query menu, click Index Tuning Wizard.
itwiz: In SQL Server 2000, index tuning can also be performed by using a command-line utility that is known as itwiz. This utility enables scripting of the index tuning process.

Back to the top

Additional reading

For more information, see the "Index Tuning Wizard for Microsoft SQL Server 2000" white paper. To view this white paper, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/library/aa902645(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa902645(SQL.80).aspx)

For more information, visit the following Microsoft Web sites:

Viewing the Query Execution Plan
http://msdn2.microsoft.com/en-us/library/aa216952(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa216952(SQL.80).aspx)

Graphically Displaying the Execution Plan Using SQL Query Analyzer
http://msdn2.microsoft.com/en-us/library/aa178423(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa178423(SQL.80).aspx)

Troubleshooting the Index Tuning Wizard
http://msdn2.microsoft.com/en-us/library/aa937579(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa937579(SQL.80).aspx)

Tuning of a Different Sort
http://msdn2.microsoft.com/en-us/library/aa496062(SQL.80).aspx (http://msdn2.microsoft.com/en-us/library/aa496062(SQL.80).aspx)

For more information, visit the following Inside SQL Server Web site:

http://www.insidesqlserver.com/index.html (http://www.insidesqlserver.com/index.html)

Delaney, Kalen. Inside Microsoft SQL Server 2000. Redmond, WA: Microsoft Press, 2000.

Microsoft provides third-party contact information to help you find technical support. This contact information may change without notice. Microsoft does not guarantee the accuracy of this third-party contact information.

Back to the top