March 24, 2016 at 6:35 am
Wondering if anyone has already generated a tool or even Tsql to parse an execution plan ( xml ) to get a runnable query. Otherwise it's a lot of manual work or try to capture it in profiler since we have a tool to convert trace sql to runnable sql with variables populated ( if it starts with exec sp_executesql N')
Variables are declared near the top of the execution plan xml
<StmtSimple StatementText="(@DerivedTable01_BUSINESS_PROCESS_STATUS30 varchar(8000),@DerivedTable01_MESSAGE_EXCHANGE_STATUS51 varchar(8000),@DerivedTable01_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID72 int,@DerivedTable01_IS_ON_HOLD_INDEFINITELY103 bit,@DerivedTable01_RECORDED_REQUEST_DATE134 datetime,@DerivedTable01_CONCRETE_TYPE155 varchar(8000),@DerivedTable01_CONCRETE_TYPE176 varchar(8000),@DerivedTable01_CONCRETE_TYPE197 varchar(8000),@DerivedTable01_CONCRETE_TYPE218 varchar(8000))
The sql statement is in that same line:
SELECT COUNT(*) AS DerivedTable01_23 FROM FILE_RECORD AS StateFileRecord02 INNER JOIN (MESSAGE AS Message13 INNER JOIN (STATE_MESSAGE_EXCHANGE AS StateMessageExchange14 LEFT OUTER JOIN SERVICE_REQUEST AS CollateralGroupRequest15 ON StateMessageExchange14.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID=CollateralGroupRequest15.SERVICE_REQUEST_ID) ON Message13.STATE_MESSAGE_EXCHANGE_ID=StateMessageExchange14.STATE_MESSAGE_EXCHANGE_ID) ON StateFileRecord02.MESSAGE_ID=Message13.MESSAGE_ID WHERE ((StateFileRecord02.FILE_ID IS NULL AND StateMessageExchange14.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS30 AND StateMessageExchange14.MESSAGE_EXCHANGE_STATUS = @DerivedTable01_MESSAGE_EXCHANGE_STATUS51 AND StateMessageExchange14.TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID = @DerivedTable01_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID72 AND ((StateMessageExchange14.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID IS NULL ) OR (CollateralGroupRequest15.IS_ON_HOLD_INDEFINITELY = @DerivedTable01_IS_ON_HOLD_INDEFINITELY103)) AND ((StateMessageExchange14.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID IS NULL ) OR (CollateralGroupRequest15.RECORDED_REQUEST_DATE <= @DerivedTable01_RECORDED_REQUEST_DATE134)) AND (CollateralGroupRequest15.CONCRETE_TYPE <> @DerivedTable01_CONCRETE_TYPE155 AND CollateralGroupRequest15.CONCRETE_TYPE <> @DerivedTable01_CONCRETE_TYPE176 AND CollateralGroupRequest15.CONCRETE_TYPE <> @DerivedTable01_CONCRETE_TYPE197 AND CollateralGroupRequest15.CONCRETE_TYPE <> @DerivedTable01_CONCRETE_TYPE218 OR (CollateralGroupRequest15.CONCRETE_TYPE IS NULL ))))" StatementId="1"
The parameter list is near the bottom which would need to be converted to SET statements:
<ParameterList>
<ColumnReference Column="@DerivedTable01_CONCRETE_TYPE218" ParameterCompiledValue="'Fdi.Po.NorthCarolinaEmergencyReleaseOfInterestRequest'" />
<ColumnReference Column="@DerivedTable01_CONCRETE_TYPE197" ParameterCompiledValue="'Fdi.Po.VirginiaEmergencyReleaseOfInterestRequest'" />
<ColumnReference Column="@DerivedTable01_CONCRETE_TYPE176" ParameterCompiledValue="'Fdi.Po.VirginiaEmergencyPaperTitleRequest'" />
<ColumnReference Column="@DerivedTable01_CONCRETE_TYPE155" ParameterCompiledValue="'Fdi.Po.ArizonaEmergencyReleaseOfInterestRequest'" />
<ColumnReference Column="@DerivedTable01_RECORDED_REQUEST_DATE134" ParameterCompiledValue="'2016-03-23 13:30:11.867'" />
<ColumnReference Column="@DerivedTable01_IS_ON_HOLD_INDEFINITELY103" ParameterCompiledValue="(0)" />
<ColumnReference Column="@DerivedTable01_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID72" ParameterCompiledValue="(12938)" />
<ColumnReference Column="@DerivedTable01_MESSAGE_EXCHANGE_STATUS51" ParameterCompiledValue="'WAITING_FOR_EXPORT'" />
<ColumnReference Column="@DerivedTable01_BUSINESS_PROCESS_STATUS30" ParameterCompiledValue="'Open'" />
</ParameterList>
March 24, 2016 at 9:00 am
The query that was used to generate the execution plan is stored with the plan. So getting a runnable query is just a matter of retrieving that information. Same thing with compile time parameter values. All stored there. I'd think generating a runnable statement from that is more work than capturing the query using extended events (don't use trace).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 24, 2016 at 11:44 am
I'll look into extended events, but usually server side tracing is a last resort -- and I realize it has to be done with a scalpel in production.
I usually go to the plan cache first to find what I'm after. This shows the query and declared variables but the real values are not there. I have to look at the parameter list in the xml to get those, which in some cases means manually putting 30 or more values. Same thing if I use Sql Sentry Plan Explorer.
From execution plan -- right click and select 'edit query text'
(@DerivedTable01_MESSAGE_EXCHANGE_KEY10 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS31 varchar(8000))SELECT StateMessageExchange02.STATE_MESSAGE_EXCHANGE_ID AS StateMessageExchange02_STATE_MESSAGE_EXCHANGE_ID4,StateMessageExchange02.LAST_MODIFIER AS StateMessageExchange02_LAST_MODIFIER6,StateMessageExchange02.LAST_MOD_DATE_TIME AS StateMessageExchange02_LAST_MOD_DATE_TIME8,StateMessageExchange02.TRANS_SEQ_NUM AS StateMessageExchange02_TRANS_SEQ_NUM10,StateMessageExchange02.MESSAGE_EXCHANGE_KEY AS StateMessageExchange02_MESSAGE_EXCHANGE_KEY0,StateMessageExchange02.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID AS StateMessageExchange02_COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID14,StateMessageExchange02.TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID AS StateMessageExchange02_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID16,StateMessageExchange02.MESSAGE_EXCHANGE_STATUS AS StateMessageExchange02_MESSAGE_EXCHANGE_STATUS18,StateMessageExchange02.BUSINESS_PROCESS_STATUS AS StateMessageExchange02_BUSINESS_PROCESS_STATUS2,StateMessageExchange02.MESSAGE_EXCHANGE_INITIATOR AS StateMessageExchange02_MESSAGE_EXCHANGE_INITIATOR22,StateMessageExchange02.CREATED_DATE_TIME AS StateMessageExchange02_CREATED_DATE_TIME24,StateMessageExchange02.CONCRETE_TYPE AS StateMessageExchange02_CONCRETE_TYPE26,StateMessageExchange02.FLORIDA_MESSAGE_EXCHANGE_STATUS AS StateMessageExchange02_FLORIDA_MESSAGE_EXCHANGE_STATUS28,StateMessageExchange02.MESSAGE_EXCHANGE_SEQUENCE_NUMBER AS StateMessageExchange02_MESSAGE_EXCHANGE_SEQUENCE_NUMBER30 FROM STATE_MESSAGE_EXCHANGE AS StateMessageExchange02 WHERE ((StateMessageExchange02.MESSAGE_EXCHANGE_KEY LIKE @DerivedTable01_MESSAGE_EXCHANGE_KEY10 AND StateMessageExchange02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS31))
From profiler trace -- can be run without editing/fixing:
exec sp_executesql N'SELECT FloridaMessageExchange02.STATE_MESSAGE_EXCHANGE_ID AS FloridaMessageExchange02_STATE_MESSAGE_EXCHANGE_ID59,FloridaMessageExchange02.LAST_MODIFIER AS FloridaMessageExchange02_LAST_MODIFIER61,FloridaMessageExchange02.LAST_MOD_DATE_TIME AS FloridaMessageExchange02_LAST_MOD_DATE_TIME63,FloridaMessageExchange02.TRANS_SEQ_NUM AS FloridaMessageExchange02_TRANS_SEQ_NUM65,FloridaMessageExchange02.MESSAGE_EXCHANGE_KEY AS FloridaMessageExchange02_MESSAGE_EXCHANGE_KEY3,FloridaMessageExchange02.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID AS FloridaMessageExchange02_COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID69,FloridaMessageExchange02.TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID AS FloridaMessageExchange02_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID1,FloridaMessageExchange02.MESSAGE_EXCHANGE_STATUS AS FloridaMessageExchange02_MESSAGE_EXCHANGE_STATUS5,FloridaMessageExchange02.BUSINESS_PROCESS_STATUS AS FloridaMessageExchange02_BUSINESS_PROCESS_STATUS7,FloridaMessageExchange02.MESSAGE_EXCHANGE_INITIATOR AS FloridaMessageExchange02_MESSAGE_EXCHANGE_INITIATOR77,FloridaMessageExchange02.CREATED_DATE_TIME AS FloridaMessageExchange02_CREATED_DATE_TIME79,FloridaMessageExchange02.CONCRETE_TYPE AS FloridaMessageExchange02_CONCRETE_TYPE0,FloridaMessageExchange02.FLORIDA_MESSAGE_EXCHANGE_STATUS AS FloridaMessageExchange02_FLORIDA_MESSAGE_EXCHANGE_STATUS83,FloridaMessageExchange02.MESSAGE_EXCHANGE_SEQUENCE_NUMBER AS FloridaMessageExchange02_MESSAGE_EXCHANGE_SEQUENCE_NUMBER85 FROM STATE_MESSAGE_EXCHANGE AS FloridaMessageExchange02 INNER JOIN SERVICE_REQUEST AS CollateralGroupRequest13 ON FloridaMessageExchange02.COLLATERAL_GROUP_REQUESTSERVICE_REQUEST_ID=CollateralGroupRequest13.SERVICE_REQUEST_ID WHERE ((FloridaMessageExchange02.TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID = @DerivedTable01_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID20 AND FloridaMessageExchange02.MESSAGE_EXCHANGE_KEY = @DerivedTable01_MESSAGE_EXCHANGE_KEY41 AND FloridaMessageExchange02.MESSAGE_EXCHANGE_STATUS = @DerivedTable01_MESSAGE_EXCHANGE_STATUS62 AND FloridaMessageExchange02.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS83 AND CollateralGroupRequest13.RECORDED_REQUEST_DATE <= @DerivedTable01_RECORDED_REQUEST_DATE114 AND CollateralGroupRequest13.IS_ON_HOLD_INDEFINITELY = @DerivedTable01_IS_ON_HOLD_INDEFINITELY135 AND CollateralGroupRequest13.BUSINESS_PROCESS_STATUS = @DerivedTable01_BUSINESS_PROCESS_STATUS156) AND ((FloridaMessageExchange02.CONCRETE_TYPE IN ( @DerivedTable01_167)) AND (CollateralGroupRequest13.CONCRETE_TYPE IN ( @DerivedTable01_178, @DerivedTable01_189, @DerivedTable01_1910, @DerivedTable01_2011, @DerivedTable01_2112, @DerivedTable01_2213, @DerivedTable01_2314, @DerivedTable01_2415, @DerivedTable01_2516, @DerivedTable01_2617, @DerivedTable01_2718, @DerivedTable01_2819, @DerivedTable01_2920, @DerivedTable01_3021, @DerivedTable01_3122, @DerivedTable01_3223, @DerivedTable01_3324, @DerivedTable01_3425, @DerivedTable01_3526, @DerivedTable01_3627, @DerivedTable01_3728, @DerivedTable01_3829, @DerivedTable01_3930, @DerivedTable01_4031, @DerivedTable01_4132, @DerivedTable01_4233, @DerivedTable01_4334, @DerivedTable01_4435, @DerivedTable01_4536, @DerivedTable01_4637, @DerivedTable01_4738, @DerivedTable01_4839, @DerivedTable01_4940, @DerivedTable01_5041, @DerivedTable01_5142, @DerivedTable01_5243, @DerivedTable01_5344, @DerivedTable01_5445, @DerivedTable01_5546, @DerivedTable01_5647, @DerivedTable01_5748, @DerivedTable01_5849))))',N'@DerivedTable01_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID20 int,@DerivedTable01_MESSAGE_EXCHANGE_KEY41 varchar(8000),@DerivedTable01_MESSAGE_EXCHANGE_STATUS62 varchar(8000),@DerivedTable01_BUSINESS_PROCESS_STATUS83 varchar(8000),@DerivedTable01_RECORDED_REQUEST_DATE114 datetime,@DerivedTable01_IS_ON_HOLD_INDEFINITELY135 bit,@DerivedTable01_BUSINESS_PROCESS_STATUS156 varchar(8000),@DerivedTable01_167 varchar(29),@DerivedTable01_178 varchar(29),@DerivedTable01_189 varchar(35),@DerivedTable01_1910 varchar(19),@DerivedTable01_2011 varchar(38),@DerivedTable01_2112 varchar(19),@DerivedTable01_2213 varchar(29),@DerivedTable01_2314 varchar(24),@DerivedTable01_2415 varchar(21),@DerivedTable01_2516 varchar(31),@DerivedTable01_2617 varchar(41),@DerivedTable01_2718 varchar(24),@DerivedTable01_2819 varchar(31),@DerivedTable01_2920 varchar(41),@DerivedTable01_3021 varchar(30),@DerivedTable01_3122 varchar(32),@DerivedTable01_3223 varchar(24),@DerivedTable01_3324 varchar(29),@DerivedTable01_3425 varchar(36),@DerivedTable01_3526 varchar(35),@DerivedTable01_3627 varchar(42),@DerivedTable01_3728 varchar(34),@DerivedTable01_3829 varchar(33),@DerivedTable01_3930 varchar(43),@DerivedTable01_4031 varchar(47),@DerivedTable01_4132 varchar(48),@DerivedTable01_4233 varchar(53),@DerivedTable01_4334 varchar(23),@DerivedTable01_4435 varchar(28),@DerivedTable01_4536 varchar(30),@DerivedTable01_4637 varchar(32),@DerivedTable01_4738 varchar(43),@DerivedTable01_4839 varchar(33),@DerivedTable01_4940 varchar(38),@DerivedTable01_5041 varchar(22),@DerivedTable01_5142 varchar(29),@DerivedTable01_5243 varchar(22),@DerivedTable01_5344 varchar(28),@DerivedTable01_5445 varchar(40),@DerivedTable01_5546 varchar(39),@DerivedTable01_5647 varchar(22),@DerivedTable01_5748 varchar(34),@DerivedTable01_5849 varchar(28)',@DerivedTable01_TITLING_AGENCY_LIENHOLDERLIENHOLDER_ID20=2956,@DerivedTable01_MESSAGE_EXCHANGE_KEY41='WillBeGeneratedWhenExportOccurs',@DerivedTable01_MESSAGE_EXCHANGE_STATUS62='WAITING_FOR_EXPORT',@DerivedTable01_BUSINESS_PROCESS_STATUS83='Open',@DerivedTable01_RECORDED_REQUEST_DATE114='2016-03-23 00:00:00',@DerivedTable01_IS_ON_HOLD_INDEFINITELY135=0,@DerivedTable01_BUSINESS_PROCESS_STATUS156='Open',@DerivedTable01_167='Fdi.Po.FloridaMessageExchange',@DerivedTable01_178='Fdi.Po.CollateralGroupRequest',@DerivedTable01_189='Fdi.Po.PortfolioItemTransferRequest',@DerivedTable01_1910='Fdi.Po.AdHocRequest',@DerivedTable01_2011='Fdi.Po.ManualDeleteConfirmationRequest',@DerivedTable01_2112='Fdi.Po.CheckRequest',@DerivedTable01_2213='Fdi.Po.CaliforniaCheckRequest',@DerivedTable01_2314='Fdi.Po.LienFilingRequest',@DerivedTable01_2415='Fdi.Po.OhioLienFiling',@DerivedTable01_2516='Fdi.Po.NewYorkLienFilingRequest',@DerivedTable01_2617='Fdi.Po.NewYorkCollateralLienFilingRequest',@DerivedTable01_2718='Fdi.Po.PaperTitleRequest',@DerivedTable01_2819='Fdi.Po.FloridaPaperTitleRequest',@DerivedTable01_2920='Fdi.Po.VirginiaEmergencyPaperTitleRequest',@DerivedTable01_3021='Fdi.Po.NevadaPaperTitleRequest',@DerivedTable01_3122='Fdi.Po.DispatchSubsequentRequest',@DerivedTable01_3223='Fdi.Po.EndOfLeaseRequest',@DerivedTable01_3324='Fdi.Po.ReleaseInterestRequest',@DerivedTable01_3425='Fdi.Po.FloridaReleaseInterestRequest',@DerivedTable01_3526='Fdi.Po.NevadaReleaseInterestRequest',@DerivedTable01_3627='Fdi.Po.SouthCarolinaReleaseInterestRequest',@DerivedTable01_3728='Fdi.Po.IdahoReleaseInterestRequest',@DerivedTable01_3829='Fdi.Po.OhioReleaseInterestRequest',@DerivedTable01_3930='Fdi.Po.OhioPaperTitleReleaseInterestRequest',@DerivedTable01_4031='Fdi.Po.ArizonaEmergencyReleaseOfInterestRequest',@DerivedTable01_4132='Fdi.Po.VirginiaEmergencyReleaseOfInterestRequest',@DerivedTable01_4233='Fdi.Po.NorthCarolinaEmergencyReleaseOfInterestRequest',@DerivedTable01_4334='Fdi.Po.NevadaTDNRequest',@DerivedTable01_4435='Fdi.Po.DuplicateTitleRequest',@DerivedTable01_4536='Fdi.Po.TitleMaintenanceRequest',@DerivedTable01_4637='Fdi.Po.ChangeOwnerAddressRequest',@DerivedTable01_4738='Fdi.Po.ConvertPaperTitleToElectronicRequest',@DerivedTable01_4839='Fdi.Po.ConvertToElectronicRequest',@DerivedTable01_4940='Fdi.Po.TitleMaintenanceViaAilotRequest',@DerivedTable01_5041='Fdi.Po.ResearchRequest',@DerivedTable01_5142='Fdi.Po.ErrorCorrectionRequest',@DerivedTable01_5243='Fdi.Po.ContractRequest',@DerivedTable01_5344='Fdi.Po.InquiryServiceRequest',@DerivedTable01_5445='Fdi.Po.VerificationInquiryServiceRequest',@DerivedTable01_5546='Fdi.Po.InformationInquiryServiceRequest',@DerivedTable01_5647='Fdi.Po.FollowUpRequest',@DerivedTable01_5748='Fdi.Po.DirectLendingServiceRequest',@DerivedTable01_5849='Fdi.Po.ReturnToClientRequest'
March 24, 2016 at 12:10 pm
Yeah, only the compile values will ever be in cache. If you need the run-time values, you have to capture them using some mechanism.
Extended Events are much safer than trace events. Although, note, I didn't say safe. You still need to be judicious in their use, especially if you intend to capture both the query metrics and the plan. It's the plan that makes it more dangerous.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 24, 2016 at 12:52 pm
Yes and in our case anyway, the compiled values are blank. I may look at some things like patindex and charindex as a way to coax out a runnable query with parameters populated since some of our ORM stuff has so many variable values ( and I'm lazy )
From plan explorer's parameter tab:
Parameter ---Compiled ValueRuntime Value
[@DerivedTable01_178]---'Fdi.Po.CollateralGroupRequest'
[@DerivedTable01_189]---'Fdi.Po.PortfolioItemTransferRequest'
[@DerivedTable01_1910]---'Fdi.Po.AdHocRequest'
[@DerivedTable01_2011]---'Fdi.Po.ManualDeleteConfirmationRequest'
[@DerivedTable01_2112]---'Fdi.Po.CheckRequest'
[@DerivedTable01_2213]---'Fdi.Po.CaliforniaCheckRequest'
[@DerivedTable01_2314]---'Fdi.Po.LienFilingRequest'
[@DerivedTable01_2415]---'Fdi.Po.OhioLienFiling'
[@DerivedTable01_2516]---'Fdi.Po.NewYorkLienFilingRequest'
[@DerivedTable01_2617]---'Fdi.Po.NewYorkCollateralLienFilingRequest'
[@DerivedTable01_2718]---'Fdi.Po.PaperTitleRequest'
[@DerivedTable01_2819]---'Fdi.Po.FloridaPaperTitleRequest'
[@DerivedTable01_2920]---'Fdi.Po.VirginiaEmergencyPaperTitleRequest'
[@DerivedTable01_3021]---'Fdi.Po.NevadaPaperTitleRequest'
[@DerivedTable01_3122]---'Fdi.Po.DispatchSubsequentRequest'
[@DerivedTable01_3223]---'Fdi.Po.EndOfLeaseRequest'
[@DerivedTable01_3324]---'Fdi.Po.ReleaseInterestRequest'
[@DerivedTable01_3425]---'Fdi.Po.FloridaReleaseInterestRequest'
[@DerivedTable01_3526]---'Fdi.Po.NevadaReleaseInterestRequest'
[@DerivedTable01_3627]---'Fdi.Po.SouthCarolinaReleaseInterestRequest'
[@DerivedTable01_3728]---'Fdi.Po.IdahoReleaseInterestRequest'
[@DerivedTable01_3829]---'Fdi.Po.OhioReleaseInterestRequest'
[@DerivedTable01_3930]---'Fdi.Po.OhioPaperTitleReleaseInterestRequest'
[@DerivedTable01_4031]---'Fdi.Po.ArizonaEmergencyReleaseOfInterestRequest'
[@DerivedTable01_4132]---'Fdi.Po.VirginiaEmergencyReleaseOfInterestRequest'
[@DerivedTable01_4233]---'Fdi.Po.NorthCarolinaEmergencyReleaseOfInterestRequest'
[@DerivedTable01_4334]---'Fdi.Po.NevadaTDNRequest'
[@DerivedTable01_4435]---'Fdi.Po.DuplicateTitleRequest'
[@DerivedTable01_4536]---'Fdi.Po.TitleMaintenanceRequest'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply