Post reply

Would like help with Exteneded Events with pair_matching

  • October 12, 2015 at 4:48 pm

    #321902

    Hello,

    I have the following as I am trying to capture any SQL (SP or plain) that doesn't complete -- So using the pair matching idea.

    But, I can only do one match at a time -- Like can only do either the sql_statement_(start and end), or sp_statement_(start or end).

    Is there any way to capture both in the same session? Or since I am adding both the events in the ADD EVENT section, can I query it somehow to get unmatched SP or SQL? Thanks for your help.

    Code:

    USE master;

    GO

    -- Create the Event Session

    IF EXISTS(SELECT *

    FROM sys.server_event_sessions

    WHERE name='TimedOutSQL')

    DROP EVENT SESSION TimedOutSQL

    ON SERVER;

    GO

    CREATE EVENT SESSION TimedOutSQL

    ON SERVER

    ADD EVENT sqlserver.sp_statement_starting (

    ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text ,sqlserver.client_hostname,sqlserver.client_app_name)

    ),

    ADD EVENT sqlserver.sp_statement_completed (

    ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text ,sqlserver.client_hostname,sqlserver.client_app_name)

    ),

    ADD EVENT sqlserver.sql_statement_starting(

    ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.client_hostname,sqlserver.client_app_name)

    ),

    ADD EVENT sqlserver.sql_statement_completed (

    ACTION(sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text ,sqlserver.client_hostname,sqlserver.client_app_name)

    )

    ADD TARGET package0.pair_matching (

    SET

    --begin_event = 'sqlserver.sp_statement_starting',

    --begin_matching_actions='sqlserver.session_id',

    --end_event='sqlserver.sp_statement_completed',

    --end_matching_actions='sqlserver.session_id'

    begin_event = 'sqlserver.sql_statement_starting',

    begin_matching_actions='sqlserver.session_id',

    end_event='sqlserver.sql_statement_completed',

    end_matching_actions='sqlserver.session_id'

    )

    WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);

    /* start the session */

    ALTER EVENT SESSION TimedOutSQL

    ON SERVER

    STATE = START;

    GO

  • October 14, 2015 at 10:25 am

    #1833300

    When you say 'does not complete' do you mean started longer than a threshold ago and is still running, Or was timed out from the application?

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • October 14, 2015 at 10:54 am

    #1833302

    Timedout from a client application. Thanks.

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

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