SQL Server Performance Forum – Threads Archive

Double Seek

Hello, I have a query that produces a queryplan that i find somewhat peculiar. The query is shown below. There is an index on the ‘outorder’ table for "status, comp_id" PK of the table is ‘comp_id, outorder’ (clustered) What I find strange is, which you see if you study the plan, that first it seeks the ‘outorder’ table for all rows with the specified statuses and "comp_id = 0". Then it seeks the table again, this time only for "comp_id = 0 " and then it merges those results. The second seek seems unneccessary to me, since the "comp_id = 0" condition is guaranteed by the first seek!? Anyone have a clue whats going on? <pre id="code"> StmtText —————————————————————————————————————————————————————————————————————— select count(*) from outorder oo (NOLOCK) inner join customer cu (NOLOCK) oncu.cust_id = oo.cust_id andcu.comp_id = 0 andcu.blockcode_id > 0 where oo.comp_id =0 and oo.status in (10,20,50) (1 row(s) affected) StmtText ———————————————————————————————————————————————————————————————————————————————————————————- |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Expr1002]=Convert([globalagg1004]))) |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[globalagg1004]=SUM([partialagg1003]))) |–Parallelism(Gather Streams) |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[partialagg1003]=Count(*))) |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[oo].[cust_id])=([cu].[cust_id])) |–Bitmap(HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[oo].[cust_id]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bitmap1006])) | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[oo].[cust_id])) | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000])=([Bmk1000]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]=[Bmk1000])) | |–Bitmap(HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bitmap1005])) | | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000])) | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[RamosITHTest].[dbo].[outorder].[s2_outorder] AS [oo]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[oo].[comp_id]=0 AND [oo].[status]=10 OR [oo].[comp_id]=0 AND [oo].[status]=20 OR [oo].[comp_id]=0 AND [oo].[status]=50) ORDERED FORWARD) | |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Bmk1000]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />PROBE([Bitmap1005])=TRUE)) | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[RamosITHTest].[dbo].[outorder].[s4_outorder] AS [oo]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[oo].[comp_id]=0) ORDERED FORWARD) |–Parallelism(Repartition Streams, PARTITION COLUMNS<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[cu].[cust_id]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />PROBE([Bitmap1006])=TRUE)) |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[RamosITHTest].[dbo].[customer].[PK_customer] AS [cu]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[cu].[comp_id]=0), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[cu].[blockcode_id]>0) ORDERED FORWARD) (15 row(s) affected) </pre id="code"> Thanks, /Linus – http://anticAPSLOCK.com</a>

the first index seek to the index s2_outorder finds the rows that meet both SARGS (status & comp_id).
next you need the cust_id to do the join.
this could be done with a bookmark lookup for each row,
or with the hash join back to the clustered index.
the second is considered more efficient because you happen to specify the cluster key value.
consider also changing the nonclustered index to status,comp_id,cust_id

That index might very well be a good idea, but I was thinking about this line: |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[RamosITHTest].[dbo].[outorder].[s4_outorder] AS [oo]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[oo].[comp_id]=0) ORDERED FORWARD) It happens parallell to the index seek on "status, comp_id" and that’s what’s confusing me. Why is that seek needed, when the first seek: |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[RamosITHTest].[dbo].[outorder].[s2_outorder] AS [oo]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[oo].[comp_id]=0 AND [oo].[status]=10 OR [oo].[comp_id]=0 AND [oo].[status]=20 OR [oo].[comp_id]=0 AND [oo].[status]=50) ORDERED FORWARD) already specifies "comp_id = 0". /Linus – http://anticAPSLOCK.com</a>

]]>