/*****************************************************************************
*   Presentation: Module 13 - Extended Events 
*   FileName:  Demo 1b - SQLskills_RecompilingProcedures_Event_Session.sql
*
*   Summary: Event Session for Module 13 Demo1
*   Summary: Create Event Session to track recompilations by database_id,
*			 demonstrates how to shred the Event Session XML, and alter the
*			 Event Session definition to change events and targets.
*
*   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. 
*
******************************************************************************/


-- If the Event Session exists Drop it
IF EXISTS (SELECT 1 
			FROM sys.server_event_sessions 
			WHERE name = 'SQLskills_RecompilingProcedures')
	DROP EVENT SESSION [SQLskills_RecompilingProcedures] ON SERVER

-- Create Event Session to find the database with most SP Recompile Events
CREATE EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
ADD EVENT sqlserver.sp_statement_starting(
	ACTION(sqlserver.database_id)
	WHERE(state = 1) -- Recompile statement_state map_key
)
ADD TARGET package0.asynchronous_bucketizer(
	SET filtering_event_name = 'sqlserver.sp_statement_starting',
		source_type = 1, -- Action
		source = 'sqlserver.database_id'),
ADD TARGET package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY=5SECONDS)
		
--Start the Event Session
ALTER EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
STATE=START	
		
		
-- RUN Workload!

-- Query target data
SELECT 
    n.value('(value)[1]', 'int') AS DatabaseID,
    DB_NAME(n.value('(value)[1]', 'int')) AS DatabaseName,
    n.value('(@count)[1]', 'int') AS RecompileEventCount,
    n.value('(@trunc)[1]', 'int') AS RecompileEventsTrunc
FROM
(SELECT CAST(target_data as XML) target_data
 FROM sys.dm_xe_sessions AS s 
 JOIN sys.dm_xe_session_targets t
     ON s.address = t.event_session_address
 WHERE s.name = 'SQLskills_RecompilingProcedures'
   AND t.target_name = 'asynchronous_bucketizer') as tab
CROSS APPLY target_data.nodes('BucketizerTarget/Slot') as q(n)

-- Modify the EVent Session 

-- Drop the sp_statement_starting event 
ALTER EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
DROP EVENT sqlserver.sp_statement_starting

-- Add the sp_statement_starting event back with predicate on database_id
ALTER EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
ADD EVENT sqlserver.sp_statement_starting(
	ACTION(sqlserver.database_id)
	WHERE(state = 1 -- Recompile statement_state map_key
		AND sqlserver.database_id = 6) -- databaseid from first capture
)

-- Drop the Bucketizer target so it can be recreated
ALTER EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
DROP TARGET package0.asynchronous_bucketizer

-- Add the Bucketizer target with 
ALTER EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
ADD TARGET package0.asynchronous_bucketizer(
	SET filtering_event_name = 'sqlserver.sp_statement_starting',
		source_type = 0, -- Event Data
		source = 'object_id')
		
/* We could have done this as we well

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

-- Create Event Session to find the specific objects recompiling
CREATE EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
ADD EVENT sqlserver.sp_statement_starting(
	ACTION(sqlserver.database_id)
	WHERE(state = 1 -- Recompile statement_state map_key
		AND sqlserver.database_id = 6) -- databaseid from first capture
)
ADD TARGET package0.asynchronous_bucketizer(
	SET filtering_event_name = 'sqlserver.sp_statement_starting',
		source_type = 0, -- Event Data
		source = 'object_id')
WITH (MAX_DISPATCH_LATENCY=5SECONDS)
		
--Start the Event Session
ALTER EVENT SESSION [SQLskills_RecompilingProcedures]
ON SERVER
STATE=START	

-- Do what makes the most sense in real life this is just a demo!  */
		

-- Query target data
SELECT 
    n.value('(value)[1]', 'int') AS ObjectID,
    OBJECT_NAME(n.value('(value)[1]', 'int'), 6) AS ObjectName,
    n.value('(@count)[1]', 'int') AS RecompileEventCount,
    n.value('(@trunc)[1]', 'int') AS RecompileEventsTrunc
FROM
(SELECT CAST(target_data as XML) target_data
 FROM sys.dm_xe_sessions AS s 
 JOIN sys.dm_xe_session_targets t
     ON s.address = t.event_session_address
 WHERE s.name = 'SQLskills_RecompilingProcedures'
   AND t.target_name = 'asynchronous_bucketizer') as tab
CROSS APPLY target_data.nodes('BucketizerTarget/Slot') as q(n)




   
SELECT 
	event_name, 
	timestamp, 
	source_database_id,
	object_id,
    SUBSTRING(OBJECT_DEFINITION(object_id),    
            (offset/2)+1, 
            (CASE offset_end 
                WHEN -1 THEN DATALENGTH(OBJECT_DEFINITION(object_id)) 
                ELSE offset_end 
              END - (offset)/2) + 1) AS statement_text
FROM
(   
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="offset"]/value)[1]', 'bigint') as offset,
	event_data.value('(event/data[@name="offset_end"]/value)[1]', 'bigint') as offset_end
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_RecompilingProcedures'
			  AND t.target_name = 'ring_buffer'
		) AS tab
		CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS split(evnt) 
	 ) AS evts(event_data)   
) AS tab2

