--监控hash_warning和sort_warning扩展事件
--创建扩展事件
CREATE event session [TempDB Spills] ON SERVER ADD event sqlserver.hash_warning (action(sqlserver.session_id, sqlserver.plan_handle, sqlserver.sql_text) WHERE (sqlserver.is_system = 0))
	,ADD event sqlserver.sort_warning (action(sqlserver.session_id, sqlserver.plan_handle, sqlserver.sql_text) WHERE (sqlserver.is_system = 0)) ADD target package0.event_file (
	SET filename = 'c:\ExtEvents\TempDB_Spiils.xel'--注意文件路径修改
	,max_file_size = 25
	)
	,ADD target package0.ring_buffer (SET max_memory = 4096)
	WITH -- Extended Events session properties 
		(
			max_memory = 4096 KB
			,event_retention_mode = allow_single_event_loss
			,max_dispatch_latency = 15 seconds
			,track_causality = OFF
			,memory_partition_mode = none
			,startup_state = OFF
			);

-- Starting Event Session 
ALTER event session [TempDB Spills] ON SERVER STATE = start;

-- Stopping Event Session 
ALTER event session [TempDB Spills] ON SERVER STATE = stop;

-- Dropping Event Session 
DROP event session [TempDB Spills] ON SERVER;
	--从文件中查看结果
	;

WITH TargetData (
	Data
	,File_Name
	,File_Offset
	)
AS (
	SELECT convert(XML, event_data) AS Data
		,file_name
		,file_offset
	FROM sys.fn_xe_file_target_read_file('c:\extevents\TempDB_Spiils*.xel', NULL, NULL, NULL)  --注意修改路劲
	)
	,EventInfo (
	[Event Time]
	,[Event]
	,SPID
	,[SQL]
	,PlanHandle
	,File_Name
	,File_Offset
	)
AS (
	SELECT Data.value('/event[1]/@timestamp', 'datetime') AS [Event Time]
		,Data.value('/event[1]/@name', 'sysname') AS [Event]
		,Data.value('(/event[1]/action[@name="session_id"]/value)[1]', 'smallint') AS [SPID]
		,Data.value('(/event[1]/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [SQL]
		,Data.value('xs:hexBinary((/event[1]/action[@name="plan_handle"]/value)[1])', 'varbinary(64)') AS [PlanHandle]
		,File_Name
		,File_Offset
	FROM TargetData
	)
SELECT ei.[Event Time]
	,ei.File_Name
	,ei.File_Offset
	,ei.[Event]
	,ei.SPID
	,ei.SQL
	,qp.Query_Plan
FROM EventInfo ei
OUTER APPLY sys.dm_exec_query_plan(ei.PlanHandle) qp


	--直接查看
	;

WITH TargetData (Data)
AS (
	SELECT convert(XML, st.target_data) AS Data
	FROM sys.dm_xe_sessions s
	JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
	WHERE s.NAME = 'TempDB Spills'
		AND st.target_name = 'ring_buffer'
	)
	,EventInfo (
	[Event Time]
	,[Event]
	,SPID
	,[SQL]
	,PlanHandle
	)
AS (
	SELECT t.e.value('@timestamp', 'datetime') AS [Event Time]
		,t.e.value('@name', 'sysname') AS [Event]
		,t.e.value('(action[@name="session_id"]/value)[1]', 'smallint') AS [SPID]
		,t.e.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [SQL]
		,t.e.value('xs:hexBinary((action[@name="plan_handle"]/value)[1])', 'varbinary(64)') AS [PlanHandle]
	FROM TargetData
	CROSS APPLY TargetData.Data.nodes('/RingBufferTarget/event') AS t(e)
	)
SELECT ei.[Event Time]
	,ei.[Event]
	,ei.SPID
	,ei.SQL
	,qp.Query_Plan
FROM EventInfo ei
OUTER APPLY sys.dm_exec_query_plan(ei.PlanHandle) qp