Post reply

Extended events query plan

  • February 16, 2021 at 5:11 pm

    #3845736

    All,

    Firstly apologises as I'm sure I'm missing the obvious but I've searched and I can't work it out.

    I have setup extended events and included both the plan_handle and query_hash actions.

    I'm retrieving the results with the following:

    set dateformat dmy

    SELECT 


    n.value('(action[@name="query_hash"]/value)[1]', 'nvarchar(128)') as query_hash,
    n.value('(action[@name="plan_handle"]/value)[1]', 'nvarchar(max)') as plan_handle
    from

    (
    select cast(event_data as xml)  event_data from

    sys.fn_xe_file_target_read_file('validpathtoxelfile', null, null, null) 

    ) result

    cross apply result.event_data.nodes('event') as q(n)


    where n.value('(@timestamp)[1]', 'datetime2') >'15/02/2021'



    order by n.value('(@timestamp)[1]', 'datetime2') desc

    An example result row is:

    query_hash: 7778086044974492365

    plan_handle: 06000100d661d11f9082e26df302000001000000000000000000000000000000000000000000000000000000

    I'm trying to use get the XML plan. I've tried different functions and both fields including:

    exec sys.dm_exec_query_plan(7778086044974492365)

    exec sys.dm_exec_query_plan(06000100d661d11f9082e26df302000001000000000000000000000000000000000000000000000000000000)

    I've also tried casting them to binary, varchar and varbinary instead but any combination I try complains of an invalid value or incorrect syntax.

    In the final version I intend to cross apply sys.dm_exec_query_plan in the main query. I'm using exec to try and fix this issue.

    My instinct is that it's either a data type problem or the wrong function.

    Thanks

     

    • This topic was modified 3 years, 9 months ago by  as_1234. Reason: Clarification

  • February 17, 2021 at 6:10 pm

    #3846350

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

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

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