SQL Server Technical Bulletin - The Index Tuning Wizard (832526)

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

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:

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.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.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 topback to the topback to the top