/*****************************************************************************
*   Presentation: DBA498 - Extended Events Deep Dive
*   FileName: Demo 8 - Configuring_Session_Options.sql 
*
*   Summary: Create Event Session to demonstrate using customizable columns.
*
*   Date: October 7, 2011 
*
*   SQL Server Versions:
*         2008, 2008R2
*         
******************************************************************************
*   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. 
*
******************************************************************************/



/********************** Memory Configuration **********************/

-- If the Event Session Exists, drop it first
IF EXISTS (SELECT 1 
			FROM sys.server_event_sessions 
			WHERE name = 'SQLskills_DefaultBuffers')
	DROP EVENT SESSION [SQLskills_DefaultBuffers] 
	ON SERVER;

-- Create the Event Session
CREATE EVENT SESSION [SQLskills_DefaultBuffers]
ON SERVER
ADD EVENT sqlserver.error_reported
GO

ALTER EVENT SESSION [SQLskills_DefaultBuffers]
ON SERVER
STATE = START;
GO

-- If the Event Session Exists, drop it first
IF EXISTS (SELECT 1 
			FROM sys.server_event_sessions 
			WHERE name = 'SQLskills_PartitionedBuffers')
	DROP EVENT SESSION [SQLskills_PartitionedBuffers] 
	ON SERVER;

-- Create the Event Session
CREATE EVENT SESSION [SQLskills_PartitionedBuffers]
ON SERVER
ADD EVENT sqlserver.error_reported
WITH (MAX_MEMORY = 4MB, 
	  EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
	  TRACK_CAUSALITY = ON,
	  MEMORY_PARTITION_MODE = PER_CPU, --2.5 buffers per CPU
	  MAX_DISPATCH_LATENCY=15SECONDS);
GO

ALTER EVENT SESSION [SQLskills_PartitionedBuffers]
ON SERVER
STATE=START;

-- Show the impact of Session Options
SELECT 
	name,
	total_regular_buffers,
	regular_buffer_Size,
	total_large_buffers,
	large_buffer_size,
	total_buffer_size,
	buffer_policy_desc,
	flag_desc
FROM sys.dm_xe_sessions
WHERE name IN ('SQLskills_DefaultBuffers', 'SQLskills_PartitionedBuffers')


-- Execute Demo 8 - Set24Processors.ps1 file to change processor counts



-- Look at the CPU Counts
SELECT *
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';

-- Start the Event Sessions Again
ALTER EVENT SESSION [SQLskills_DefaultBuffers]
ON SERVER
STATE = START;
GO

ALTER EVENT SESSION [SQLskills_PartitionedBuffers]
ON SERVER
STATE=START;

-- Show the impact of Session Options
SELECT 
	name,
	total_regular_buffers,
	regular_buffer_Size,
	total_large_buffers,
	large_buffer_size,
	total_buffer_size,
	buffer_policy_desc,
	flag_desc
FROM sys.dm_xe_sessions
WHERE name IN ('SQLskills_DefaultBuffers', 'SQLskills_PartitionedBuffers')

/********************** Startup State **********************/

ALTER EVENT SESSION [SQLskills_DefaultBuffers]
ON SERVER
STATE = STOP;
GO

ALTER EVENT SESSION [SQLskills_DefaultBuffers]
ON SERVER
WITH (STARTUP_STATE = ON);
GO

-- Execute Demo 8 - Remove24Processors.ps1 file to reset processor counts


-- Show the Session started automatically with the instance
SELECT 
	name,
	total_regular_buffers,
	regular_buffer_Size,
	total_large_buffers,
	large_buffer_size,
	total_buffer_size,
	buffer_policy_desc,
	flag_desc
FROM sys.dm_xe_sessions
WHERE name IN ('SQLskills_DefaultBuffers', 'SQLskills_PartitionedBuffers')


/********************** Causality Tracking **********************/

/*
USE AdventureWorks2008R2;
GO
IF OBJECT_ID('ExecuteLotsOfStatements') IS NOT NULL
BEGIN
	DROP PROCEDURE dbo.ExecuteLotsOfStatements
END
GO
CREATE PROCEDURE dbo.ExecuteLotsOfStatements
(@ExecutionLoopCount INT = 100)
AS

DECLARE @Loop INT = 0;
WHILE @Loop < @ExecutionLoopCount
BEGIN
	DECLARE @Loop2 INT
	SELECT @Loop2 = @Loop
	
	SET @Loop = @Loop+1
END

GO
*/


IF EXISTS (SELECT 1 
			FROM sys.server_event_sessions 
			WHERE name = 'SQLskills_TrackCausalityDemo')
	-- Drop the Event Session to cleanup Demo
	DROP EVENT SESSION [SQLskills_TrackCausalityDemo]
	ON SERVER;

-- Create an Event Session to capture Errors Reported
CREATE EVENT SESSION [SQLskills_TrackCausalityDemo]
ON SERVER
ADD EVENT sqlserver.module_start,
ADD EVENT sqlserver.module_end,
ADD EVENT sqlserver.sp_statement_starting,
ADD EVENT sqlserver.sp_statement_completed,
ADD EVENT sqlserver.lock_acquired
ADD TARGET package0.ring_buffer
	--(SET max_memory=128,
	--	 occurrence_number=100)
WITH (MAX_DISPATCH_LATENCY = 1 SECONDS, TRACK_CAUSALITY=ON);
GO

-- ALTER the Event Session and Start it.
ALTER EVENT SESSION [SQLskills_TrackCausalityDemo]
ON SERVER
STATE=START;
GO

EXECUTE AdventureWorks2008R2.dbo.ExecuteLotsOfStatements @ExecutionLoopCount = 10;
GO 

ALTER EVENT SESSION [SQLskills_TrackCausalityDemo]
ON SERVER
DROP EVENT sqlserver.module_start,
DROP EVENT sqlserver.module_end,
DROP EVENT sqlserver.sp_statement_starting,
DROP EVENT sqlserver.sp_statement_completed,
DROP EVENT sqlserver.lock_acquired
GO

-- Parse out Event Columns and Activity_ID and Event Sequence
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="resource_type"]/text)[1]', 'nvarchar(4000)') AS [resource_type],
	event_data.value('(event/data[@name="mode"]/text)[1]', 'nvarchar(4000)') AS [mode],
	event_data.value('(event/data[@name="owner_type"]/text)[1]', 'nvarchar(4000)') AS [owner_type],
	event_data.value('(event/data[@name="transaction_id"]/value)[1]', 'bigint') AS [transaction_id],
	event_data.value('(event/data[@name="database_id"]/value)[1]', 'int') AS [database_id],
	event_data.value('(event/data[@name="lockspace_workspace_id"]/value)[1]', 'nvarchar(4000)') AS [lockspace_workspace_id],
	event_data.value('(event/data[@name="lockspace_sub_id"]/value)[1]', 'bigint') AS [lockspace_sub_id],
	event_data.value('(event/data[@name="lockspace_nest_id"]/value)[1]', 'bigint') AS [lockspace_nest_id],
	event_data.value('(event/data[@name="resource_0"]/value)[1]', 'bigint') AS [resource_0],
	event_data.value('(event/data[@name="resource_1"]/value)[1]', 'bigint') AS [resource_1],
	event_data.value('(event/data[@name="resource_2"]/value)[1]', 'bigint') AS [resource_2],
	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]', 'bigint') AS [object_id],
	event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS [duration],
	event_data.value('(event/data[@name="row_count"]/value)[1]', 'bigint') AS [row_count],
	event_data.value('(event/data[@name="object_type"]/value)[1]', 'nvarchar(4000)') AS [object_type],
	event_data.value('(event/data[@name="object_name"]/value)[1]', 'nvarchar(4000)') AS [object_name],
	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/data[@name="writes"]/value)[1]', 'bigint') AS [writes],
	event_data.value('(event/data[@name="state"]/text)[1]', 'nvarchar(4000)') AS [state],
	event_data.value('(event/data[@name="offset"]/value)[1]', 'int') AS [offset],
	event_data.value('(event/data[@name="offset_end"]/value)[1]', 'int') AS [offset_end],
	event_data.value('(event/data[@name="nest_level"]/value)[1]', 'int') AS [nest_level],
	CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 1, 36) AS uniqueidentifier) as activity_id,
	CAST(SUBSTRING(event_data.value('(event/action[@name="attach_activity_id"]/value)[1]', 'varchar(50)'), 38, 10) AS int) as event_sequence
FROM 
(   SELECT XEvent.query('.') AS event_data 
    FROM 
    (    -- Cast the target_data to XML 
        SELECT CAST(target_data AS XML) AS TargetData 
        FROM sys.dm_xe_session_targets st 
        JOIN sys.dm_xe_sessions s 
            ON s.address = st.event_session_address 
        WHERE name = 'SQLskills_TrackCausalityDemo' 
          AND target_name = 'ring_buffer' 
    ) AS Data 
    -- Split out the Event Nodes 
    CROSS APPLY TargetData.nodes ('RingBufferTarget/event') AS XEventData (XEvent)   
) AS tab (event_data)
