SQL Server Performance Forum – Threads Archive
Help for Clustered Index Scan in Execution Plan
Hi all<br /><br />I have this result of a SP and i need to understand the clustered index part. I have tried to understabd and at some extend i do know ( by looking T-SQL) why this happening. but do not know what would be ideal approach to follow. There is some clustered Index Scan and one Index Scan there. some of Clustered Index Sacn are cost 0% so i imagine they would be fine.<br /><br />so please help me…<br /><br />Result of Execution plan: ********START Here **********<br />|–Merge Join(Concatenation)<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[Book].[Booking] ASC, [Invline].[Supplier] ASC, [Expr1021] ASC, [Invhead].[Created] ASC, [Expr1019] ASC, [SEG].[City2] ASC, [Expr1020] ASC, [SEG].[Time2] ASC, [Invline].[ID] ASC, [Invhead].[Invno] ASC, [Invhead].[Issuedate] AS<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1019]=isnull([SEG].[City1], Convert([Book].[Destination])), [Expr1020]=isnull([SEG].[Time1], [Book].[Departuredate]), [Expr1021]=Convert([Invline].[Product]), [Expr1022]=If ([Invhead].[Total]>=0.00) then ‘D’ <br /> | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([@suppinvno]=” OR isnull([ap_transhead].[supp_invno], ”)=[@suppinvno]) AND ([@journalid]=0 OR isnull([ap_transhead].[journalid], 0)=[@journalid])))<br /> | |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transhead].[id])=([ap_transline].[ap_transheadid]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transhead].[id]=[ap_transline].[ap_transheadid]))<br /> | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transhead].[PK_ap_transhead]))<br /> | |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transline].[invlineid])=([Invline].[ID]))<br /> | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transline].[IDX_AP_Transheadid]))<br /> | |–Hash Match(Left Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invline].[SegmentID])=([SEG].[ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[SEG].[ID]=[Invline].[SegmentID] AND [Invline].[SegmentID]>0))<br /> | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([@closed]=NULL OR Convert([@closed])=1) OR isnull([Expr1003], 0)=Convert([@closed])))<br /> | | |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transline].[invlineid])=([Invline].[ID]))<br /> | | |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transline].[invlineid]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=MAX([ap_transline].[closed])))<br /> | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transline].[IDX_UC_ap_transline_invlineid]), ORDERED FORWARD)<br /> | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Invoiceno])=([Invline].[Invoiceno]))<br /> | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[company].[company])=([Book].[Company]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[company].[company]=[Book].[Company]))<br /> | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[company].[idx_mastercomp]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[company].[master]=[@company]) ORDERED FORWARD)<br /> | | | |–Merge Join(Inner Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Book].[Booking])=([Invhead].[Booking]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Booking]=[Book].[Booking]))<br /> | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[Book].[PK_Book]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([@depdate1]=NULL OR [Book].[Departuredate]>=[@depdate1]) AND ([@depdate2]=NULL OR [Book].[Departuredate]<[@depda<br /> | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[Invhead].[booking]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Status]>=3 AND ([@booking]=0 OR [Invhead].[Booking]=[@booking])) ORDERED FORWARD)<br /> | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[Invline].[invoiceno]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invline].[Supplier]<>’BSP’ AND ([@supplier]=NULL OR [Invline].[Supplier]=[@supplier])))<br /> | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[SEG].[booking]))<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Book].[Booking] ASC, [Expr1050] ASC, [Invhead].[Created] ASC, [Expr1048] ASC, [SEG].[City2] ASC, [Expr1049] ASC, [SEG].[Time2] ASC, [Invline].[ID] ASC))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1048]=isnull([SEG].[City1], Convert([Book].[Destination])), [Expr1049]=isnull([SEG].[Time1], [Book].[Departuredate]), [Expr1050]=If ([Invline].[Product]=[@TaxProduct]) then ‘XBSPSETT’ else ‘FBSPSETT’, [Expr10<br /> |–Hash Match(Left Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invline].[SegmentID])=([SEG].[ID]))<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Invoiceno])=([Invline].[Invoiceno]))<br /> | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[@journalid]=0 OR [Expr1101]))<br /> | | |–Nested Loops(Left Semi Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[@journalid]=0)OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Booking]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1101] = [PROBE VALUE]))<br /> | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[@suppinvno]=” OR [Expr1100]))<br /> | | | |–Nested Loops(Left Semi Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[@suppinvno]=”)OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Booking]), DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1100] = [PROBE VALUE]))<br /> | | | |–Merge Join(Inner Join, MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Book].[Booking])=([Invhead].[Booking]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Booking]=[Book].[Booking]))<br /> | | | | |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Book].[Booking] ASC))<br /> | | | | | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[company].[company])=([Book].[Company]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Book].[Company]=[company].[company]))<br /> | | | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[company].[idx_mastercomp]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[company].[master]=[@company]) ORDERED FORWARD)<br /> | | | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[Book].[PK_Book]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([@depdate1]=NULL OR [Book].[Departuredate]>=[@depdate1]) AND ([@depdate2]=NULL OR [Book].[Departuredate]<[@depdat<br /> | | | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[Invhead].[booking]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invhead].[Status]>=3 AND ([@booking]=0 OR [Invhead].[Booking]=[@booking])) ORDERED FORWARD)<br /> | | | |–Row Count Spool<br /> | | | |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transhead].[id]=[ap_transline].[ap_transheadid]))<br /> | | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transline].[UCAPMethod]=3))<br /> | | | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Bmk1037]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transline]))<br /> | | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TIX].[ticketno]))<br />
| | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[TIX].[booking]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TIX].[booking]=[Invhead].[Booking]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TIX].[status]=0) ORDERED FORWARD)<br /> | | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transline].[IDX_TicketNo]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transline].[ticketno]=[TIX].[ticketno]) ORDERED FORWARD)<br /> | | | |–Table Spool<br /> | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transhead].[IDX_Supp_InvNo]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transhead].[supp_invno]=[@suppinvno]) ORDERED FORWARD)<br /> | | |–Row Count Spool<br /> | | |–Nested Loops(Inner Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transhead].[id]=[ap_transline].[ap_transheadid]))<br /> | | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transline].[UCAPMethod]=3))<br /> | | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Bmk1043]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transline]))<br /> | | | |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TIX].[ticketno]))<br /> | | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[TIX].[booking]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TIX].[booking]=[Invhead].[Booking]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[TIX].[status]=0) ORDERED FORWARD)<br /> | | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transline].[IDX_TicketNo]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transline].[ticketno]=[TIX].[ticketno]) ORDERED FORWARD)<br /> | | |–Table Spool<br /> | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[ap_transhead].[IDX_JournalID]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[ap_transhead].[journalid]=[@journalid]) ORDERED FORWARD)<br /> | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />STARTUP EXPR([@supplier]=NULL OR [@supplier]=’BSP’ AND [@closed]=NULL OR Convert([@closed])=1)))<br /> | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[Invline].[invoiceno]), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Invline].[SegmentID]>0 AND [Invline].[Supplier]=’BSP’))<br /> |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Myt_Prod].[dbo].[SEG].[booking]))<br /><br />********* END Here *********
http://www.sql-server-performance.com/query_execution_plan_analysis.asp will guide in this regard. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
you need to run this query with the SET STATISTICS PROFILE ON, then send then full output of the plan, which will have the stuff above plus estimate and actual row counts etc, for us to make a more meaningful assessment
]]>