I’m a really big fan of using operator properties for a lot of things, at least visually. Where things sort of fall down for that is copying and pasting things out.
For some stuff, you still need to head down to the XML.
Let’s say you have a stored procedure that accepts a bunch of parameters. The rest of this one isn’t important, but here you go:
CREATE OR ALTER PROCEDURE dbo.AwesomeSearchProcedure ( @OwnerUserId int = NULL, @CreationDate datetime = NULL, @LastActivityDate datetime = NULL, @PostTypeId int = NULL, @Score int = NULL, @Title nvarchar(250) = NULL, @Body nvarchar(MAX) = NULL )
A Plan Appears
Let’s say we grab a query plan for this thing from the plan cache or query store. We can get the properties of the select operator and see compile time values:
EXEC dbo.AwesomeSearchProcedure @OwnerUserId = 35004, @CreationDate = '20130101', @LastActivityDate = '20140101', @Title = N'SQL Server';
We get this back:
Again — nice visually — but it doesn’t do much for us if we want to recreate executing the stored procedure to get an actual execution plan.
It’s also not terrible helpful if we want to simulate a parameter sniffing situation, because we only have the compile time values, not the run time values.
Bummer. But whatever.
If we right click and select “show execution plan XML”, we can scroll way down to the bottom to find the XML fragment that holds what the properties display:
<ParameterList> <ColumnReference Column="@iTitle" ParameterDataType="nvarchar(250)" ParameterCompiledValue="N'SQL Server'" /> <ColumnReference Column="@iLastActivityDate" ParameterDataType="datetime" ParameterCompiledValue="'2014-01-01 00:00:00.000'" /> <ColumnReference Column="@iCreationDate" ParameterDataType="datetime" ParameterCompiledValue="'2013-01-01 00:00:00.000'" /> <ColumnReference Column="@iOwnerUserId" ParameterDataType="int" ParameterCompiledValue="(35004)" /> </ParameterList>
This still isn’t awesome, because we have to do some surgery on the XML itself to get values out.
It’s even worse if we have a parameterized application query, because not only do we need to make a DECLARE to assign values to these variables but we need to turn the query itself into dynamic SQL.
If we don’t do that, we’ll fall victim to a common pitfall: testing queries with local variables.
For most things, I absolutely adore using operator properties. For some things, you still need the XML.
It’d be nice if there were some fancy copy and paste magic that would do that for you, but so far it doesn’t exist.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Common Table Expressions Are Useful For Rewriting Scalar Functions In SQL Server
- SQL Server 2017 CU 30: The Real Story With SelOnSeqPrj Fixes
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
10 thoughts on “Getting Parameter Values From A SQL Server Query Plan For Performance Tuning”
“It’d be nice if there were some fancy copy and paste magic that would do that for you”
Maybe not c/p, but could this hypothetical magic shred that bit of XML from the plan and spit out a table of variables with their corresponding DTs and values? I could see that being useful when grabbing a plan from the cache. Like ‘select whatever from sys.dm_exec_cached_plans as cp cross apply magic(cp.plan_handle)”
I wrote that into sp_BlitzCache. Look at the “Cached Execution Parameter” column.
Then I’m missing something. What do you wish existed that doesn’t?
… An easy way to copy and paste all of the values out of the GUI rather than go to the XML.
I’ve had instances where the plan XML doesn’t have the compiled values in it ,which drives me nuts. Have you seen that before or know what causes it? Maybe it’s just me 😀
Holy cow that was it! Changed my statement to dynamic sql (with a parameter) and now I can see both compiled and runtime values!
Never realized that before for why sometimes I could see both and sometimes only runtime values. Thank you!
Cruddy little buggers eh?
Yeah! I’m not normally a frustrate-able type of person but that was frustrating! 😀
Comments are closed.