Post reply

Xevents and plan_handle

  • November 14, 2016 at 8:51 am

    #332117

    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

    #1912470

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply