September 10, 2013 at 1:24 pm
Hello --- when looking at a simple query plan for a select with "Include Actual Execution Plan" enabled in sql server mgmt studio (SSMS), I see a ParameterCompiledValue in the XML plan as well as a ParameterRuntimeValue in it.
However, if I capture this same query in another tab using sys.dm_exec_query_plan and providing the plan handle of the currently running select statement, I get the same plan, but it does *not* have the ParameterRuntimeValue. It only has ParameterCompiledValue. Otherwise the plan is the exact same.
If I change up parameter values, I still see only the original ParameterCompiledValue via sys.dm_exec_query_plan but I can see both ParameterCompiledValue and the ParameterRuntimeValue when looking at SSMS "Include Actual Execution Plan" output. Any ideas why sys.dm_exec_query_plan does not show ParameterRuntimeValue?
Thanks!
September 10, 2013 at 3:24 pm
The plans stored in the cache do not carry run-time values with them. They only have the compile time values. This is perfectly normal behavior. The only way to get run-time information is to capture the plan while executing using SSMS or extended events.
"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
September 10, 2013 at 4:06 pm
Grant Fritchey (9/10/2013)
The only way to get run-time information is to capture the plan while executing using SSMS or extended events.
You can also capture them with SQL Trace. Note that capturing actual plans with Trace or X-Events is very expensive, because even if you filter for a single spid, the event is activated for all processes.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 10, 2013 at 7:44 pm
Thanks both for your input!
September 11, 2013 at 4:15 am
Erland Sommarskog (9/10/2013)
Grant Fritchey (9/10/2013)
The only way to get run-time information is to capture the plan while executing using SSMS or extended events.You can also capture them with SQL Trace. Note that capturing actual plans with Trace or X-Events is very expensive, because even if you filter for a single spid, the event is activated for all processes.
Trace? What's that?
Kidding.
"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
September 11, 2013 at 6:38 am
Grant Fritchey (9/11/2013)
Erland Sommarskog (9/10/2013)
Grant Fritchey (9/10/2013)
The only way to get run-time information is to capture the plan while executing using SSMS or extended events.You can also capture them with SQL Trace. Note that capturing actual plans with Trace or X-Events is very expensive, because even if you filter for a single spid, the event is activated for all processes.
Trace? What's that?
Kidding.
OH NO YOU DIDN'T!! Microsoft can turn off Profiler when they pry it from my cold, dead hands!! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 11, 2013 at 7:15 am
TheSQLGuru (9/11/2013)
OH NO YOU DIDN'T!! Microsoft can turn off Profiler when they pry it from my cold, dead hands!! :hehe:
Step into the light. Extended Events are better in every possible way. Except they don't work with distributed replay. Oh, and they can't be directly integrated with Performance Monitor metrics. And, of course, if you're stuck on versions older than 2012 there's no built-in GUI. But other than those piddling things, they're better in every possible way.
"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
September 11, 2013 at 10:19 am
Grant Fritchey (9/11/2013)
Step into the light. Extended Events are better in every possible way. Except they don't work with distributed replay. Oh, and they can't be directly integrated with Performance Monitor metrics. And, of course, if you're stuck on versions older than 2012 there's no built-in GUI. But other than those piddling things, they're better in every possible way.
Yeah, but Profiler is probably prommed into Kevin's brain. As it is into mine.
Yes, I should use X-events, but if I need to look at something quickly, quickly means Profiler the way it is today. Not the least when there are too many SQL 2008 around.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 11, 2013 at 1:57 pm
HAH - good one Grant!! And Erland is correct about Profiler.
Oh, and I LOVE DBSophic's Qure product!! That thing makes me look like a WIZARD at clients, especially new ones, where I can find bad stuff with some shiny GUIfied aggregate Profiler analyses!! I have of course had scripts to do that for pushing 15 years now, but the Qure tool is MUCH better - and is one of utilities I am including in my "TheSQLGuru's Free and Low-Cost SQL Server Tools" SQL Saturday presentation I am currently creating! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply