none
Monitoring stored proced with the extended events - SQL Server 2008 R2/2012

    Question

  • Hi,

    in order to monitor a stored procedure and the possible long running queries I think to use this script:

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Trace')
        DROP EVENT SESSION [Trace] ON SERVER;
    CREATE EVENT SESSION [Trace]
    ON SERVER
    ADD EVENT sqlserver.sp_statement_completed(
         ACTION (sqlserver.plan_handle, sqlserver.sql_text)
         WHERE (([sqlserver].[database_id]=(5) AND [object_id]=(18099105) AND [duration]>(1000)))),
    ADD EVENT sqlserver.sql_statement_completed(
         ACTION (sqlserver.plan_handle, sqlserver.sql_text)
         WHERE (([sqlserver].[database_id]=(5) AND [object_id]=(18099105) AND [duration]>(1000))))
    ADD TARGET package0.asynchronous_file_target(
         SET filename='d:\xevents\Trace.xel', metadatafile='d:\xevents\Trace.xem')
    WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 300 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)

    Ok? Do I need adding something?

    Thanks

    Monday, October 21, 2013 1:47 PM

All replies

  • You can also use SSMS --> Object Explorer --> Database --> Standard Reports --> Object Execution Statistics

    Buck Woody: SQL Server Management Studio Standard Reports – Object Execution Statistics: [DatabaseName]


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, October 21, 2013 2:06 PM
    Moderator
  • Ok, interesting, but I'd like to test the execution of the stored procedure launched by a batch job.

    Thanks

    Monday, October 21, 2013 2:25 PM
  • Hi,

    inside a step job I've written this script:

    IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='Trace')
        DROP EVENT SESSION [Trace] ON SERVER;
    CREATE EVENT SESSION [Trace]
    ON SERVER
    ADD EVENT sqlserver.sp_statement_completed(
         ACTION (sqlserver.plan_handle, sqlserver.sql_text)
         WHERE (([sqlserver].[database_id]=(5) AND [object_id]=(18099105) AND [duration]>(1000)))),
    ADD EVENT sqlserver.sql_statement_completed(
         ACTION (sqlserver.plan_handle, sqlserver.sql_text)
         WHERE (([sqlserver].[database_id]=(5) AND [object_id]=(18099105) AND [duration]>(1000))))
    ADD TARGET package0.asynchronous_file_target(
         SET filename='X:\xevents\Trace.xel', metadatafile='X:\xevents\Trace.xem')
    WITH (MAX_MEMORY = 4096KB, EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 300 SECONDS, MAX_EVENT_SIZE = 0KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF)
    
    GO
    
    ALTER EVENT SESSION [Trace] ON SERVER STATE = START;
    GO
    --
    
    exec myProc
    
    --
    
    ALTER EVENT SESSION [Trace] ON SERVER STATE = STOP;
    GO

    When I run this SELECT statement:

    SELECT name, target_name, CAST(xet.target_data AS xml)
    FROM sys.dm_xe_session_targets AS xet
    JOIN sys.dm_xe_sessions AS xe
       ON (xe.address = xet.event_session_address)
    WHERE xe.name = 'Trace'

    I cannot see nothing about the Trace event also if I comment the WHERE clause.

    I've tried to select the sys.databases view and I can find the database.

    I've tried to select the sys.objects view and I can find the stored procedure.

    I've tried to run

    SELECT * FROM sys.server_event_sessions

    and I can see my event session, but when I run

    SELECT * FROM sys.dm_xe_session_targets
    select * from sys.dm_xe_sessions

    I cannot see anything related to the Trace event.

    However, the trace.* files was created in the specified folder.

    Any suggests to me, please?

    Thanks

    Tuesday, October 22, 2013 8:47 AM