CREATE DATABASE production;

GO

USE production;

GO

CREATE TABLE t1 (c1 INT IDENTITY, c2 UNIQUEIDENTIFIER ROWGUIDCOL DEFAULT NEWID(), c3 CHAR (5000) DEFAULT 'a');

CREATE CLUSTERED INDEX t1_CL ON t1 (c1);

CREATE NONCLUSTERED INDEX t1_NCL ON t1 (c2);

GO

SET NOCOUNT ON;

INSERT INTO t1 DEFAULT VALUES;

GO 1000

--Get the database ID to plug into the event session

SELECT DB_ID ('production');

GO

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'EE_ExpensiveQueries')

    DROP EVENT SESSION EE_ExpensiveQueries ON SERVER;

GO

CREATE EVENT SESSION EE_ExpensiveQueries ON SERVER

ADD EVENT sqlserver.sql_statement_completed

(ACTION (sqlserver.sql_text, sqlserver.plan_handle)

WHERE sqlserver.database_id = 14 /*DBID*/ AND CPU_time > 10 /*total ms of CPU time*/)

ADD TARGET package0.asynchronous_file_target

(SET FILENAME = N'C:\SQL_Data\EE_ExpensiveQueries.xel', METADATAFILE = N'C:\SQL_Data\EE_ExpensiveQueries.xem')

WITH (max_dispatch_latency = 1 seconds);

GO

ALTER EVENT SESSION EE_ExpensiveQueries ON SERVER STATE = START;

GO

USE production;

GO

SELECT COUNT (*) FROM t1 WHERE c1 > 500;

GO

SELECT SUM (c1) FROM t1 WHERE c3 LIKE 'a';

GO

ALTER INDEX t1_CL ON t1 REORGANIZE;

GO

USE master;

GO

SELECT COUNT (*) FROM sys.fn_xe_file_target_read_file

('C:\SQL_Data\EE_ExpensiveQueries*.xel', 'C:\SQL_Data\EE_ExpensiveQueries*.xem', NULL, NULL);

GO

SELECT data FROM

(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file

('C:\SQL_Data\EE_ExpensiveQueries*.xel', 'C:\SQL_Data\EE_ExpensiveQueries*.xem', NULL, NULL)

) entries;

GO

SELECT

data.value (

'(/event[@name=''sql_statement_completed'']/@timestamp)[1]', 'DATETIME') AS [Time],

data.value (

'(/event/data[@name=''cpu'']/value)[1]', 'INT') AS [CPU (ms)],

CONVERT (FLOAT, data.value ('(/event/data[@name=''duration'']/value)[1]', 'BIGINT')) / 1000000

AS [Duration (s)],

data.value (

'(/event/action[@name=''sql_text'']/value)[1]', 'VARCHAR(MAX)') AS [SQL Statement],

SUBSTRING (data.value ('(/event/action[@name=''plan_handle'']/value)[1]', 'VARCHAR(100)'), 0, 100)

AS [Plan Handle]

FROM

(SELECT CONVERT (XML, event_data) AS data FROM sys.fn_xe_file_target_read_file

('C:\SQL_Data\EE_ExpensiveQueries*.xel', 'C:\SQL_Data\EE_ExpensiveQueries*.xem', null, null)

) entries

ORDER BY [Time] DESC;

GO

SELECT [query_plan] FROM sys.dm_exec_query_plan (0x06000e00faf5b11e80166a4e0200000001000000000000000000000000000000000000000000000000000000);

GO