/*****************************************************************************
*   Presentation: Module 13 - Extended Events 
*   FileName:  Demo 4 - SQLskills_FirstNExecutions_Event_Session.sql
*
*   Summary: Session generator for Module 13 Demo4
*   Summary: Demonstrates how to use restrict the number of times an event
*			 can be fired.
*
*   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 counter predicate sources
SELECT 
    p.name AS package_name,
    o.name AS predicate_name,
    o.description
FROM sys.dm_xe_packages AS p
INNER JOIN sys.dm_xe_objects AS o
    ON p.guid = o.package_guid
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
  AND o.object_type = 'pred_source'
  AND o.name LIKE '%counter%'


-- If the Event Session exists drop it
IF EXISTS(SELECT * 
         FROM sys.server_event_sessions 
         WHERE name='SQLskills_FirstNExecutions') 
    DROP EVENT SESSION [SQLskills_FirstNExecutions] ON SERVER; 

-- Create the event session
CREATE EVENT SESSION [SQLskills_FirstNExecutions]
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
( ACTION (sqlserver.sql_text)
  WHERE (package0.counter <=5))
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS)
GO

-- Start the event session
ALTER EVENT SESSION [SQLskills_FirstNExecutions]
ON SERVER
STATE = START
GO

-- Run example workload
SELECT @@VERSION
GO
SELECT @@SERVERNAME
GO
SELECT @@SPID
GO
SELECT @@VERSION
GO
SELECT @@SERVERNAME
GO
SELECT @@SPID
GO

-- Drop the Event to stop collection
ALTER EVENT SESSION [SQLskills_FirstNExecutions]
ON SERVER
DROP EVENT sqlserver.sql_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,
    n.value('(event/@package)[1]', 'varchar(50)') AS package_name,
    DATEADD(hh, 
            DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP), 
            n.value('(event/@timestamp)[1]', 'datetime2')) AS [timestamp],  
    n.value('(event/data[@name="object_id"]/value)[1]', 'int') AS [object_id],
    n.value('(event/data[@name="object_type"]/value)[1]', 'nvarchar(128)') AS [object_type],
    n.value('(event/data[@name="duration"]/value)[1]', 'int') AS [duration],
    n.value('(event/data[@name="cpu"]/value)[1]', 'int') AS [cpu],
    n.value('(event/data[@name="reads"]/value)[1]', 'int') AS [reads],
    n.value('(event/data[@name="writes"]/value)[1]', 'int') AS [writes],
    n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text]
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_FirstNExecutions'
		  AND t.target_name = 'ring_buffer'
	) AS sub
	CROSS APPLY target_data.nodes('RingBufferTarget/event') AS q(td)
) as tab
GO

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