/*****************************************************************************
*   Presentation: Module 13 - Extended Events 
*   FileName:  Demo 3 - SQLskills_SpStatementTimeouts_Event_Session.sql
*
*   Summary: Session generator for Module 13 Demo3
*   Summary: Demonstrates how to capture sp_statement_starting events that 
*			 don't complete due to a timeout.
*
*   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. 
*
******************************************************************************/

-- Create the Event Session
CREATE EVENT SESSION [SQLskills_SpStatementTimeouts]
ON SERVER
ADD EVENT sqlserver.sp_statement_starting
(
	ACTION(sqlserver.session_id, sqlserver.tsql_stack, sqlserver.sql_text, sqlserver.plan_handle)
	WHERE(state = 0) --Don't fire Recompiled Events
),
ADD EVENT sqlserver.sp_statement_completed
(
	ACTION(sqlserver.session_id, sqlserver.tsql_stack)
)
ADD TARGET package0.pair_matching
(
	SET begin_event = 'sqlserver.sp_statement_starting',
		begin_matching_actions = 'sqlserver.session_id, sqlserver.tsql_stack',
		end_event = 'sqlserver.sp_statement_completed',
		end_matching_actions = 'sqlserver.session_id, sqlserver.tsql_stack',
		respond_to_memory_pressure = 0
)
WITH(MAX_DISPATCH_LATENCY=1 SECONDS, 
	TRACK_CAUSALITY=ON, 
	STARTUP_STATE = ON)
-- Start the Event Session
ALTER EVENT SESSION SQLskills_SpStatementTimeouts
ON SERVER
STATE = START
GO

-- Run a test workload
EXECUTE AdventureWorks2008R2.dbo.StoredProcedureExceedsDuration
GO 10
EXECUTE AdventureWorks2008R2.dbo.StoredProcedureExceedsDuration '00:00:30.000'
GO
EXECUTE AdventureWorks2008R2.dbo.StoredProcedureExceedsDuration
GO 10


-- Drop the Event to stop collection
ALTER EVENT SESSION [SQLskills_SpStatementTimeouts]
ON SERVER
DROP EVENT sqlserver.sp_statement_starting,
DROP EVENT sqlserver.sp_statement_completed
GO

-- Wait in a delay for Events to Buffer
WAITFOR DELAY '00:00:05'
GO

-- Query the XML to get the Target Data
SELECT 
	n.value('(event/@name)[1]', 'varchar(50)') AS event_name, 
	DATEADD(hh, 
		DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
		n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp], 
	n.value('(event/action[@name="session_id"]/value)[1]', 'int') AS [session_id],
	n.value('(event/data[@name="source_database_id"]/value)[1]', 'int') AS [source_database_id],
	n.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id], 
	n.value('(event/data[@name="object_type"]/text)[1]', 'int') AS [object_type], 
	n.value('(event/data[@name="state"]/text)[1]', 'nvarchar(4000)') AS [state],
	n.value('(event/data[@name="offset"]/value)[1]', 'int') AS [offset],
	n.value('(event/data[@name="offset_end"]/value)[1]', 'int') AS [offset_end],
	n.value('(event/data[@name="nest_level"]/value)[1]', 'int') AS [nest_level], 
	CAST(n.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(4000)') AS XML) AS [plan_handle], 
	CAST(n.value('(event/action[@name="tsql_stack"]/value)[1]', 'nvarchar(4000)') AS XML) AS [tsql_stack], 
	REPLACE(n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'), CHAR(10), CHAR(13)+CHAR(10)) AS [sql_text],
	CAST(SUBSTRING(n.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id, 
	CAST(SUBSTRING(n.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int) as event_sequence 
FROM
(	SELECT td.query('.') as n
	FROM 
	(
		SELECT CAST(target_data AS XML) as target_data
		FROM sys.dm_xe_sessions AS s 
		JOIN sys.dm_xe_session_targets AS t 
			ON t.event_session_address = s.address
		WHERE s.name = 'SQLskills_SpStatementTimeouts'
		  AND t.target_name = 'pair_matching'
	) AS sub
	CROSS APPLY target_data.nodes('PairingTarget/event') AS q(td)
) as tab
GO

-- Drop the Event Session
DROP EVENT SESSION [SQLskills_SpStatementTimeouts]
ON SERVER