Following on from my last blog post I now want to run through how to identify large queries using Extended Events.

Extended Events were introduced in SQL Server 2008 and allow monitoring to be run against a server with a lower performance impact than profiler sessions and server side traces.

There’s now a nice GUI in SQL Server 2012 in which you can setup your Extended Event session but I always like to start with creating a session using T-SQL:-

USE [master];
GO
CREATE EVENT SESSION [QueriesWith200kReads] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE ([logical_reads]>200000))
ADD TARGET package0.event_file(SET filename=N'C:\SQLServer\XEvents\QueriesWith200kReads.xel')
GO

The script above will capture all SQL batches executed on the server that perform over 200,000 reads. I’ve set the target as a .XEL file and have defined what information to capture.

The session can then be started:-

ALTER EVENT SESSION [QueriesWith200kReads]
ON SERVER
STATE = START;
GO

While the session is running, the following can be executed to see how many queries have been captured:-

SELECT COUNT(*)
FROM sys.fn_xe_file_target_read_file
 ('C:\SQLServer\XEvents\QueriesWith200kReads*.xel',NULL,NULL,NULL);
GO

To query the data, XQUERY can be used. The below script grabs all the data from the session in a CTE and then combines it to give an overall view so that we can see which queries are being executed the most and are causing the most pain!

WITH CTE_ExecutedSQLStatements AS
(SELECT
[XML Data],
[XML Data].value('(/event[@name=''sql_statement_completed'']/@timestamp)[1]','DATETIME')AS [Time],
[XML Data].value('(/event/data[@name=''duration'']/value)[1]','int')AS [Duration],
[XML Data].value('(/event/data[@name=''cpu_time'']/value)[1]','int')AS [CPU],
[XML Data].value('(/event/data[@name=''logical_reads'']/value)[1]','int')AS [logical_reads],
[XML Data].value('(/event/data[@name=''physical_reads'']/value)[1]','int')AS [physical_reads],
[XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')AS [SQL Statement]
FROM
(SELECT 
OBJECT_NAME AS [Event], 
CONVERT(XML, event_data) AS [XML Data]
FROM 
sys.fn_xe_file_target_read_file
('C:\SQLServer\XEvents\QueriesWith200kReads*.xel',NULL,NULL,NULL)) as v)
SELECT
[SQL Statement]AS [SQL Statement],
SUM(Duration)AS [Total Duration],
SUM(CPU)AS [Total CPU],
SUM(Logical_Reads)AS [Total Logical Reads],
SUM(Physical_Reads) AS [Total Physical Reads]
FROM
CTE_ExecutedSQLStatements
GROUP BY
[SQL Statement]
ORDER BY
[Total Logical Reads] DESC
GO

But what if we want to change the session? In SQL Server 2012 a GUI was introduced so sessions can be setup and changed easily.

At the moment, the session picks up queries that are performing over 200,000 logical reads. What if we wanted to also include any queries that are performing a large amount of physical reads?

In order to do this, in Object Explorer go to Management > Extended Events > Sessions.

Right click the session and then go to properties.

Go to Events.

Click configure.

Click the filter tab.

Select OR in the And/Or box and then select Physical Reads, Greater than (>) and 10000 in the adjacent boxes.

Then either click OK or Script. I always script out the changes that I have made so that I can re-create the session if needed:-

ALTER EVENT SESSION [QueriesWith200kReads] ON SERVER 
DROP EVENT sqlserver.sql_batch_completed
ALTER EVENT SESSION [QueriesWith200kReads] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack,sqlserver.username)
    WHERE (([package0].[greater_than_uint64]([logical_reads],(200000))) OR ([physical_reads]>(10000))))
GO

Finally there is also the ability to watch events as they are captured (think profiler session with a lot less impact).

Again in Object Explorer go to Management > Extended Events > Sessions.

Right click the session and select Watch Live Data.

Columns can be added by right clicking the existing columns and selecting Choose Columns.

And there you have it, how to setup and alter an Extended Event session using scripts and/or the GUI.