Scribd Logo
Upload

Welcome to Scribd!

  • 89 views

    SQL Queries

    Uploaded by

    El hussein Sayed
    This document contains SQL queries to create an event session on the server named "EE_SlowQueryLog" that captures SQL statements that take over 500 milliseconds to complete. It filters out certain waits and traffic from stored procedures. The event data is written to files on the C: drive. The session is then started.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    SQL Queries

    Uploaded by

    El hussein Sayed
    89 views1 page
    This document contains SQL queries to create an event session on the server named "EE_SlowQueryLog" that captures SQL statements that take over 500 milliseconds to complete. It filters out certain waits and traffic from stored procedures. The event data is written to files on the C: drive. The session is then started.

    Original Description:

    Sql query

    Original Title

    Sql queries

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains SQL queries to create an event session on the server named "EE_SlowQueryLog" that captures SQL statements that take over 500 milliseconds to complete. It filters out certain waits and traffic from stored procedures. The event data is written to files on the C: drive. The session is then started.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    89 views1 page

    SQL Queries

    Uploaded by

    El hussein Sayed
    This document contains SQL queries to create an event session on the server named "EE_SlowQueryLog" that captures SQL statements that take over 500 milliseconds to complete. It filters out certain waits and traffic from stored procedures. The event data is written to files on the C: drive. The session is then started.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 1

    Sql queries

    USE MASTER;
    GO

    /* Conditionally drop the session if it already exists */


    IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name =
    'EE_SlowQueryLog')
    DROP EVENT SESSION EE_SlowQueryLog ON SERVER;
    GO

    /* Create the session */


    CREATE EVENT SESSION EE_SlowQueryLog ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    ( ACTION (
    sqlserver.client_app_name
    ,sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.database_name
    ,sqlserver.plan_handle
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE duration > 500000 /* 500 milliseconds in microseconds */
    AND sql_text NOT LIKE 'WAITFOR (RECEIVE message_body FROM
    WMIEventProviderNotificationQueue)%' /* Exclude WMI waits */
    AND sql_text NOT LIKE '%sp_GetSlowQueries%' /* Exclude traffic from stored
    procedure to read data */)
    ADD TARGET package0.asynchronous_file_target
    ( SET FILENAME = N'C:\ModernDBA\EE_SlowQueryLog.xel', METADATAFILE =
    N'C:\ModernDBA\EE_SlowQueryLog.xem')
    WITH (max_dispatch_latency = 1 seconds);
    GO

    /* Start Session */
    ALTER EVENT SESSION EE_SlowQueryLog ON SERVER STATE = START;
    GO

    You might also like