Scribd
Upload
128 views1 page

SQL Performance Monitoring Guide

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.

Uploaded by

El hussein Sayed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
128 views1 page

SQL Performance Monitoring Guide

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.

Uploaded by

El hussein Sayed
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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
,[Link])
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