Identifying large queries using Extended Events

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.

XEvents Blog - Pic 1

Right click the session and then go to properties.

XEvents Blog - Pic 2

Go to Events.

XEvents Blog - Pic 3

Click configure.

XEvents Blog - Pic 4

Click the filter tab.

XEvents Blog - Pic 5

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

XEvents Blog - Pic 6

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.

XEvents Blog - Pic 7

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.

Advertisements

2 thoughts on “Identifying large queries using Extended Events

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s