Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SHOWPLAN_TEXT

Author  Topic 

sqlnovice123
Constraint Violating Yak Guru

262 Posts
Posted - 2010-02-24 : 16:13:45
I was unable to use the FAQ on show plan for investigating performance issues with the query.

How can I tell by the below?

|--Table Insert(OBJECT:([#Response]), SET:([#Response].[InquiryDate] = [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate],[#Response].[InquiryID] = [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID],[#Response].[Client] = [pivotal].[dbo].[CompanyMaster].[Company_Name],[#Response].[CPLevel] = [Expr1022],[#Response].[DealerCount] = [Expr1023],[#Response].[ResponseCount] = [Expr1024],[#Response].[ExecutedDealer] = [Expr1025],[#Response].[ExecutedPrice] = [Expr1026],[#Response].[ExecutedLevel] = [Expr1027],[#Response].[CoverLevel] = [Expr1028],[#Response].[CoverDealer] = [Expr1029],[#Response].[TradeYear] = [tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear],[#Response].[TradeMonthName] = [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName],[#Response].[TradeMonth] = [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth]))
|--Top(ROWCOUNT est 0)
|--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
|--Hash Match(Inner Join, HASH:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID])=([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]), RESIDUAL:([tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] = [tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] AND [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] = [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] AND [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] = [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] AND [pivotal].[dbo].[CompanyMaster].[Company_Name] = [pivotal].[dbo].[CompanyMaster].[Company_Name] AND [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] = [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] AND [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID] = [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID])=([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]), RESIDUAL:([tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] = [tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear] AND [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] = [tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth] AND [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] = [tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName] AND [pivotal].[dbo].[CompanyMaster].[Company_Name] = [pivotal].[dbo].[CompanyMaster].[Company_Name] AND [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] = [MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate] AND [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID] = [MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
| |--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
| | |--Compute Scalar(DEFINE:([Expr1023]=CONVERT_IMPLICIT(int,[Expr1110],0)))
| | |--Stream Aggregate(GROUP BY:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]) DEFINE:([Expr1110]=Count(*)))
| | |--Sort(DISTINCT ORDER BY:([trd].[TradeYear] ASC, [trd].[TradeMonth] ASC, [trd].[TradeMonthName] ASC, [pivotal].[dbo].[CompanyMaster].[Company_Name] ASC, [inq].[InquiryDate] ASC, [inq].[SystemInquiryID] ASC, [res].[CPPivotalCompanyID] ASC))
| | |--Table Spool
| | |--Compute Scalar(DEFINE:([Expr1038]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[CPLevel] as [rsl].[CPLevel] IS NOT NULL THEN [MISTrading].[dbo].[Response].[CPPivotalCompanyID] as [res].[CPPivotalCompanyID] ELSE NULL END))
| | |--Parallelism(Gather Streams)
| | |--Hash Match(Inner Join, HASH:([pivotal].[dbo].[CompanyMaster].[Company_Id])=([res].[CPPivotalCompanyID]), RESIDUAL:([MISTrading].[dbo].[Response].[CPPivotalCompanyID] as [res].[CPPivotalCompanyID]=[pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | | |--Table Scan(OBJECT:([pivotal].[dbo].[CompanyMaster]), WHERE:(CONVERT_IMPLICIT(numeric(2,2),isnull([pivotal].[dbo].[CompanyMaster].[MA_CommissionTS],'0'),0)<>(0.25)))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([res].[CPPivotalCompanyID]))
| | |--Hash Match(Inner Join, HASH:([inq].[MISInquiryID])=([rsl].[MISInquiryID]))
| | |--Hash Match(Inner Join, HASH:([inq].[MISInquiryID])=([inq].[MISInquiryID]))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([inq].[MISInquiryID]))
| | | | |--Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_3] AS [inq]), WHERE:([MISTrading].[dbo].[Inquiry].[IsVolume] as [inq].[IsVolume]=(1) AND [MISTrading].[dbo].[Inquiry].[InquiryType] as [inq].[InquiryType]='F'))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([inq].[MISInquiryID]))
| | | |--Index Scan(OBJECT:([MISTrading].[dbo].[Inquiry].[IX_Inquiry_2] AS [inq]))
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([res].[MISInquiryID])=([rsl].[MISInquiryID]), RESIDUAL:([MISTrading].[dbo].[ResponseLeg].[MISInquiryID] as [rsl].[MISInquiryID]=[MISTrading].[dbo].[Response].[MISInquiryID] as [res].[MISInquiryID] AND [MISTrading].[dbo].[Response].[MISResponseID] as [res].[MISResponseID]=[MISTrading].[dbo].[ResponseLeg].[MISResponseID] as [rsl].[MISResponseID]))
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([res].[MISInquiryID]), ORDER BY:([res].[MISInquiryID] ASC))
| | | |--Index Scan(OBJECT:([MISTrading].[dbo].[Response].[IX_Response_4] AS [res]), ORDERED FORWARD)
| | |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([rsl].[MISInquiryID])=([iql].[MISInquiryID]), RESIDUAL:([MISTrading].[dbo].[InquiryLeg].[MISInquiryID] as [iql].[MISInquiryID]=[MISTrading].[dbo].[ResponseLeg].[MISInquiryID] as [rsl].[MISInquiryID] AND [MISTrading].[dbo].[InquiryLeg].[LegSequence] as [iql].[LegSequence]=[MISTrading].[dbo].[ResponseLeg].[LegSequence] as [rsl].[LegSequence]))
| | |--Compute Scalar(DEFINE:([Expr1030]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='T' THEN [MISTrading].[dbo].[ResponseLeg].[Price] as [rsl].[Price] ELSE NULL END, [Expr1031]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='T' THEN [MISTrading].[dbo].[ResponseLeg].[CPLevel] as [rsl].[CPLevel] ELSE NULL END, [Expr1032]=CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='C' THEN [MISTrading].[dbo].[ResponseLeg].[CPLevel] as [rsl].[CPLevel] ELSE NULL END))
| | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([rsl].[MISInquiryID]), ORDER BY:([trd].[MISInquiryID] ASC))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([rsl].[MISResponseLegID], [Expr1109]) WITH ORDERED PREFETCH)
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:([trd].[MISInquiryID], [Expr1108]) WITH ORDERED PREFETCH)
| | | | |--Sort(ORDER BY:([trd].[MISInquiryID] ASC))
| | | | | |--Hash Match(Inner Join, HASH:([pivotal].[dbo].[CompanyMaster].[Company_Id])=([trd].[ClientID]), RESIDUAL:([tempdb].[dbo].[#Trade].[ClientID] as [trd].[ClientID]=[pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([pivotal].[dbo].[CompanyMaster].[Company_Id]))
| | | | | | |--Table Scan(OBJECT:([pivotal].[dbo].[CompanyMaster]), WHERE:(CONVERT_IMPLICIT(numeric(2,2),isnull([pivotal].[dbo].[CompanyMaster].[MA_CommissionTS],'0'),0)<>(0.25)))
| | | | | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([trd].[ClientID]))
| | | | | |--Table Scan(OBJECT:([tempdb].[dbo].[#Trade] AS [trd]))
| | | | |--Index Seek(OBJECT:([MISTrading].[dbo].[ResponseLeg].[IX_ResponseLeg_1] AS [rsl]), SEEK:([rsl].[MISInquiryID]=[tempdb].[dbo].[#Trade].[MISInquiryID] as [trd].[MISInquiryID]) ORDERED FORWARD)
| | | |--Clustered Index Seek(OBJECT:([MISTrading].[dbo].[ResponseLeg].[PK_ResponseLeg] AS [rsl]), SEEK:([rsl].[MISResponseLegID]=[MISTrading].[dbo].[ResponseLeg].[MISResponseLegID] as [rsl].[MISResponseLegID]) LOOKUP ORDERED FORWARD)
| | |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([iql].[MISInquiryID]), ORDER BY:([iql].[MISInquiryID] ASC))
| | |--Index Scan(OBJECT:([MISTrading].[dbo].[InquiryLeg].[IX_InquiryLeg_3] AS [iql]), WHERE:([MISTrading].[dbo].[InquiryLeg].[IsVolume] as [iql].[IsVolume]=(1)) ORDERED FORWARD)
| |--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
| |--Compute Scalar(DEFINE:([Expr1024]=CONVERT_IMPLICIT(int,[Expr1111],0)))
| |--Stream Aggregate(GROUP BY:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]) DEFINE:([Expr1111]=COUNT([Expr1038])))
| |--Sort(DISTINCT ORDER BY:([trd].[TradeYear] ASC, [trd].[TradeMonth] ASC, [trd].[TradeMonthName] ASC, [pivotal].[dbo].[CompanyMaster].[Company_Name] ASC, [inq].[InquiryDate] ASC, [inq].[SystemInquiryID] ASC, [Expr1038] ASC))
| |--Table Spool
|--Compute Scalar(DEFINE:([trd].[TradeYear]=[tempdb].[dbo].[#Trade].[TradeYear] as [trd].[TradeYear], [trd].[TradeMonth]=[tempdb].[dbo].[#Trade].[TradeMonth] as [trd].[TradeMonth], [trd].[TradeMonthName]=[tempdb].[dbo].[#Trade].[TradeMonthName] as [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name]=[pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate]=[MISTrading].[dbo].[Inquiry].[InquiryDate] as [inq].[InquiryDate], [inq].[SystemInquiryID]=[MISTrading].[dbo].[Inquiry].[SystemInquiryID] as [inq].[SystemInquiryID]))
|--Stream Aggregate(GROUP BY:([trd].[TradeYear], [trd].[TradeMonth], [trd].[TradeMonthName], [pivotal].[dbo].[CompanyMaster].[Company_Name], [inq].[InquiryDate], [inq].[SystemInquiryID]) DEFINE:([Expr1022]=MAX([tempdb].[dbo].[#Trade].[CPLevel] as [trd].[CPLevel]), [Expr1025]=MAX(CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='T' THEN replace([pivotal].[dbo].[CompanyMaster].[Short_Name_1],'_US','') ELSE NULL END), [Expr1026]=MAX([Expr1030]), [Expr1027]=MAX([Expr1031]), [Expr1028]=MAX([Expr1032]), [Expr1029]=MAX(CASE WHEN [MISTrading].[dbo].[ResponseLeg].[State] as [rsl].[State]='C' THEN replace([pivotal].[dbo].[CompanyMaster].[Short_Name_1],'_US','') ELSE NULL END)))
|--Sort(ORDER BY:([trd].[TradeYear] ASC, [trd].[TradeMonth] ASC, [trd].[TradeMonthName] ASC, [pivotal].[dbo].[CompanyMaster].[Company_Name] ASC, [inq].[InquiryDate] ASC, [inq].[SystemInquiryID] ASC))
|--Table Spool

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts
Posted - 2010-02-24 : 22:36:17
XML format are more easily readable.
Any special reason for saving the Plan in txt format instead of XML format.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-02-25 : 01:48:38
See discussion: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140333
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site