November 14, 2016 at 8:51 am
Hi,
i run Xevents to capture sqlserver.sp_statement_completed with sqlserver.plan_handle
when i query the output file i see in the event_data that the query is removing the first 2 character in the event/action[@name="plan_handle"]/value)[1]
why it is happens and how i can pass the plan_handle from the Xevents to sys.dm_exec_query_plan?
THX
November 15, 2016 at 6:27 am
If you mean that you are looking at the value for parameterized_plan_handle and it's showing something like this:
0x
What you're seeing is the handle for a plan that has not been parameterized. If you provide a parameterized query, you'll see a different value. For example, running this query:
SELECT *
FROM Sales.BuyingGroups AS bg;
Results in 0x. However, this query:
SELECT *
FROM Sales.BuyingGroups AS bg
WHERE bg.BuyingGroupID = 42;
Results in:
0x060007009A489A12E0FAC22DA3000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
This is because the first query is not parameterized while the second query is. If you want to capture the plan_handle for all statements, I'd suggest adding the action plan_handle.
Just be cautious adding actions because they do come with added cost.
"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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply