Microsoft KB Archive/832526

= SQL Server Technical Bulletin - The Index Tuning Wizard =

Article ID: 832526

Article Last Modified on 4/25/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



Goal: To use the Index Tuning Wizard to help improve the performance of a query.

back to the top 

Example
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:

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) 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 &quot;Identification&quot; 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.
 * --Clustered Index Seek(OBJECT:([mydb].[dbo].[WORK_QUE].[PK_WORK_QUE] AS [C]), |--Nested

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:
 * In the left pane, click a server.
 * 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 &quot;Index Tuning Wizard for Microsoft SQL Server 2000&quot; white paper. To view this white paper, visit the following Microsoft Web site:

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

Graphically Displaying the Execution Plan Using SQL Query Analyzer

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

Tuning of a Different Sort

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

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

Keywords: kbtypenonkb kbpubtypett kbscan kbquery kbperformance kbwizard kbserver kbdatabase kbinfo KB832526

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.