/*****************************************************************************
*   Presentation: Module 13 - Extended Events 
*   FileName:  Demo 2a - SQLskills_StatementExceedsLastDuration_Event_Session.sql
*
*   Summary: Session generator for Module 13 Demo2
*   Summary: Demonstrates how to use complex predicators to minimize event
*			 collection for rapid analysis.
*
*   Date: March 14, 2011 
*
*   SQL Server Versions:
*         2008, 2008 R2
*         
******************************************************************************
*   Copyright (C) 2011 Jonathan M. Kehayias, SQLskills.com
*   All rights reserved. 
*
*   For more scripts and sample code, check out 
*      http://sqlskills.com/blogs/jonathan
*
*   You may alter this code for your own *non-commercial* purposes. You may
*   republish altered code as long as you include this copyright and give 
*	due credit. 
*
*
*   THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
*   ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
*   TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
*   PARTICULAR PURPOSE. 
*
******************************************************************************/

-- Find the object columns for sp_statement_completed Event
SELECT 
	name, 
	column_id, 
	[type_name]
FROM sys.dm_xe_object_columns
WHERE [object_name] = 'sp_statement_completed'
  AND column_type = 'data'

-- Find the predicate comparator for greater than max int64
SELECT 
	p.name,
	o.name,
	o.description 
FROM sys.dm_xe_objects AS o
JOIN sys.dm_xe_packages AS p
	ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = 'pred_compare'
  AND o.name like '%greater%max%int64%'

-- Find the predicate source columns available
SELECT 
	p.name,
	o.name,
	o.description
FROM sys.dm_xe_objects AS o
JOIN sys.dm_xe_packages AS p
	ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
  AND o.object_type = 'pred_source'

/* Setup the Test Procedure
USE AdventureWorks2008R2
GO
IF OBJECT_ID('StoredProcedureExceedsDuration') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.StoredProcedureExceedsDuration
END
GO
CREATE PROCEDURE dbo.StoredProcedureExceedsDuration
( @WaitForValue varchar(12) = '00:00:00:050')
AS
BEGIN
	WAITFOR DELAY @WaitForValue;	
END
GO
*/

EXECUTE AdventureWorks2008R2.dbo.StoredProcedureExceedsDuration '00:00:02.050'

IF EXISTS(SELECT * 
         FROM sys.server_event_sessions 
         WHERE name='SQLskills_StatementExceedsLastDuration') 
    DROP EVENT SESSION [SQLskills_StatementExceedsLastDuration] ON SERVER; 
GO
DECLARE @ObjectID NVARCHAR(20) = OBJECT_ID('StoredProcedureExceedsDuration')
DECLARE @DatabaseID NVARCHAR(10) = DB_ID('AdventureWorks2008R2')
DECLARE @SqlCmd NVARCHAR(2000)='
CREATE EVENT SESSION [SQLskills_StatementExceedsLastDuration]
ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
	ACTION (sqlserver.sql_text)
	WHERE	(object_id = ' + @ObjectID + ' AND 
			 sqlserver.database_id = ' + @DatabaseID + ' AND
			 package0.greater_than_max_int64(duration, 1000000)))
ADD TARGET package0.ring_buffer(
	SET MAX_MEMORY=2048)
WITH (MAX_DISPATCH_LATENCY=5SECONDS);'
PRINT @SqlCmd
EXECUTE(@SqlCmd)
GO
ALTER EVENT SESSION [SQLskills_StatementExceedsLastDuration]
ON SERVER
STATE=START;
GO

-- Run Workload!!



-- Query the Active Session DMV's for the target_data
SELECT CAST(target_data AS xml) AS TargetData
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
	ON s.address = t.event_session_address
WHERE s.name = 'SQLskills_StatementExceedsLastDuration'
  AND t.target_name = 'ring_buffer'
  
  
-- Breaking the target_data down by Event
SELECT n.query('.') AS event_data
FROM(
SELECT CAST(target_data AS xml) AS TargetData
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
	ON s.address = t.event_session_address
WHERE s.name = 'SQLskills_StatementExceedsLastDuration'
  AND t.target_name = 'ring_buffer') AS tab
CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS r(n)



-- Shred events out of the target
SELECT
	event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
	DATEADD(hh, 
		DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
		event_data.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],
	event_data.value('(event/data[@name="source_database_id"]/value)[1]', 'int') as source_database_id,
	event_data.value('(event/data[@name="object_id"]/value)[1]', 'int') as object_id,
	event_data.value('(event/data[@name="object_type"]/value)[1]', 'int') as object_type,
	event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') as duration,
	event_data.value('(event/data[@name="cpu"]/value)[1]', 'bigint') as cpu,
	event_data.value('(event/data[@name="reads"]/value)[1]', 'bigint') as reads,
	event_data.value('(event/action[@name="writes"]/value)[1]', 'bigint') as writes,
	event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM(	SELECT evnt.query('.') AS event_data
		FROM
		(	SELECT CAST(target_data AS xml) AS TargetData
			FROM sys.dm_xe_sessions AS s
			JOIN sys.dm_xe_session_targets AS t
				ON s.address = t.event_session_address
			WHERE s.name = 'SQLskills_StatementExceedsLastDuration'
			  AND t.target_name = 'ring_buffer'
		) AS tab
		CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS split(evnt) 
	 ) AS evts(event_data)

-- Shred events out of the target (alternative)
DECLARE @target_data XML
SELECT @target_data = CAST(target_data AS xml)
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
	ON s.address = t.event_session_address
WHERE s.name = 'SQLskills_StatementExceedsLastDuration'
  AND t.target_name = 'ring_buffer'
  
SELECT
	event_data.value('(@name)[1]', 'varchar(50)') AS event_name,
	DATEADD(hh, 
		DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
		event_data.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
	event_data.value('(data[@name="source_database_id"]/value)[1]', 'int') as source_database_id,
	event_data.value('(data[@name="object_id"]/value)[1]', 'int') as object_id,
	event_data.value('(data[@name="object_type"]/value)[1]', 'int') as object_type,
	event_data.value('(data[@name="duration"]/value)[1]', 'bigint') as duration,
	event_data.value('(data[@name="cpu"]/value)[1]', 'bigint') as cpu,
	event_data.value('(data[@name="reads"]/value)[1]', 'bigint') as reads,
	event_data.value('(action[@name="writes"]/value)[1]', 'bigint') as writes,
	event_data.value('(action[@name="sql_text"]/value)[1]', 'varchar(max)') as sql_text
FROM @target_data.nodes ('RingBufferTarget/event') AS split(event_data) 
