August 11, 2011 at 4:37 pm
Hi there,
I use the below query as part of some monitoring I do to analyse locks that cause other transactions to be blocked.
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
where req.session_id <> @@spid
In most cases the query doing the blocking is a stored procedure, which I can get from the above statement. However it gives me the stored proc definition, when I would like to know what parameters are being passed to it at the time.
Does anybody know how I can retrieve this information without running a trace against the server?
thanks, Mark
August 11, 2011 at 6:40 pm
I haven't seen it consistently show in execution plans. This query will help you get closer to what you need though. First off, you will be looking for something like this in the xml execution plan.
<ParameterList>
<ColumnReference Column="@P0" ParameterCompiledValue="(81065)" />
</ParameterList>
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
QPX.query_plan AS XMLPLan
FROM sys.dm_exec_requests req
FULL OUTER JOIN sys.dm_exec_cached_plans CP
ON cp.plan_handle = req.plan_handle
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
OUTER APPLY sys.dm_exec_query_plan(ISNULL(req.plan_handle,CP.plan_handle)) AS QPX
where req.session_id <> @@spid
Using the above query, you will be able to click the XMLPlan value in the output and it will show the execution plan for the query in question. Then right click that graphical execution plan and select show execution plan xml.
With a bit more work on this query, you could use a little xquery and extract the parameter values without the extra steps I outlined.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2011 at 7:35 pm
If you want the current value and not the compile values (or both) , you need to use profiler performance xml plan with statistics profile. Which will containt the real actual execution and those values. The only way I know how to get those short of running it manually in SSMS with actual plan.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply