-- 高开销查询：Capturing expensive queries   
	CREATE event session [Expensive Queries] ON SERVER ADD event sqlserver.sql_statement_completed (
		action(sqlserver.plan_handle) WHERE (
			(
				cpu_time >= 5000000
				OR -- Time in microseconds 
				logical_reads >= 10000
				OR writes >= 10000
				)
			AND sqlserver.is_system = 0
			)
		)
		,ADD event sqlserver.rpc_completed (
		WHERE (
			(
				cpu_time >= 5000000
				OR -- Time in microseconds 
				logical_reads >= 10000
				OR writes >= 10000
				)
			AND sqlserver.is_system = 0
			)
		) ADD target package0.event_file (SET filename = 'c:\ExtEvents\Expensive Queries.xel')--注意修改路径
		WITH (event_retention_mode = allow_single_event_loss);
			
			
	--读取结果，可能比较久
	;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('D:\ExtendedEvent\Expensive*.xel', NULL, NULL, NULL)--修改路径
		)
		,EventInfo (
		[Event]
		,[Event Time]
		,[CPU Time]
		,[Duration]
		,[Logical Reads]
		,[Physical Reads]
		,[Writes]
		,[Rows]
		,[Statement]
		,[PlanHandle]
		,File_Name
		,File_Offset
		)
	AS (
		SELECT Data.value('/event[1]/@name', 'sysname') AS [Event]
			,Data.value('/event[1]/@timestamp', 'datetime') AS [Event Time]
			,Data.value('((/event[1]/data[@name="cpu_time"]/value/text())[1])', 'bigint') AS [CPU Time]
			,Data.value('((/event[1]/data[@name="duration"]/value/text())[1])', 'bigint') AS [Duration]
			,Data.value('((/event[1]/data[@name="logical_reads"]/value/text())[1])', 'int') AS [Logical Reads]
			,Data.value('((/event[1]/data[@name="physical_reads"]/value/text())[1])', 'int') AS [Physical Reads]
			,Data.value('((/event[1]/data[@name="writes"]/value/text())[1])', 'int') AS [Writes]
			,Data.value('((/event[1]/data[@name="row_count"]/value/text())[1])', 'int') AS [Rows]
			,Data.value('((/event[1]/data[@name="statement"]/value/text())[1])', 'nvarchar(max)') AS [Statement]
			,Data.value('xs:hexBinary(((/event[1]/action[@name="plan_handle"]/value/text())[1]))', 'varbinary(64)') AS [PlanHandle]
			,File_Name
			,File_Offset
		FROM TargetData
		)
	SELECT ei.[Event]
		,ei.[Event Time]
		,ei.[CPU Time] / 1000 AS [CPU Time (ms)]
		,ei.[Duration] / 1000 AS [Duration (ms)]
		,ei.[Logical Reads]
		,ei.[Physical Reads]
		,ei.[Writes]
		,ei.[Rows]
		,ei.[Statement]
		,ei.[PlanHandle]
		,ei.File_Name
		,ei.File_Offset
		,qp.Query_Plan
	FROM EventInfo ei
	OUTER APPLY sys.dm_exec_query_plan(ei.PlanHandle) qp