将扩展事件（慢SQL）内容插入到表中并预警

建立慢SQL的扩展事件见：点击打开链接

扩展事件是个好东西， 但是手动打开比较麻烦， 而且还比较慢。

下面的存储过程可以获取到相关的数据， 并插入到 DBA_SlowQueryEvent 表中。 用一个作业每5分钟执行一次此存储过程。

因为是取增量数据， 此存储过程也并不慢， IO好点的机器一般在1秒内。

以后查慢SQL，只需要查 DBA_SlowQueryEvent 表即可， 做预警也方便了。

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Proc_DBA_GetSlowQueryEvent]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].Proc_DBA_GetSlowQueryEvent
GO
-- =============================================
-- Author:		yenange
-- Create date: 2017-08-23
-- Description:	获取慢查询扩展事件的内容并插入到表
-- =============================================
CREATE PROCEDURE [dbo].Proc_DBA_GetSlowQueryEvent
AS
BEGIN
	SET NOCOUNT ON;
	--0. 如果不存在则创建表
	IF OBJECT_ID('dbo.DBA_SlowQueryEvent') IS NULL
	BEGIN
		CREATE TABLE dbo.DBA_SlowQueryEvent(
			eventId BIGINT IDENTITY(1,1) NOT NULL,
			eventTime DATETIME2,
			databaseName nvarchar(max),
			objectName NVARCHAR(MAX),
			[statement] nvarchar(max),
			[elapsedSeconds] AS CAST(duration/1000000.0 AS DECIMAL(15,2)) persisted,
			[cpuSeconds] AS CAST(cpu_time/1000000.0 AS DECIMAL(15,2)) persisted,
			logical_reads_MB AS (CAST(logical_reads AS BIGINT)*8060)/(1024*1024) persisted,
			physical_reads_MB AS (CAST(physical_reads AS BIGINT)*8060)/(1024*1024) persisted,
			duration BIGINT,
			cpu_time BIGINT,
			physical_reads BIGINT,
			logical_reads BIGINT,
			eventName nvarchar(128),
			username nvarchar(max),
			[checked] BIT NOT NULL DEFAULT(0),
			CONSTRAINT PK_DBA_SlowQueryEvent PRIMARY KEY NONCLUSTERED(
				eventId	
			)
		)
		CREATE INDEX IX_DBA_SlowQueryEvent_eventTime ON dbo.DBA_SlowQueryEvent( eventTime )
	END
	
	--1. 获取慢查询扩展事件的路径
	DECLARE @path NVARCHAR(500)
	SELECT @path=convert(xml,b.target_data).value('((//EventFileTarget/File)[1]/@name)[1]','varchar(max)')
	FROM [sys].[dm_xe_sessions] a
	left join [sys].[dm_xe_session_targets] b on a.[address]=b.event_session_address
	WHERE a.name='slow_query'
	
	--2. 获取上次的获取的最后的时间，如果没有记录，取最近30分钟
	DECLARE @lastTime DATETIME2
	SELECT @lastTime=ISNULL( (SELECT MAX(eventTime) FROM dbo.DBA_SlowQueryEvent), DATEADD(n,-30,GETDATE()) )
	
	--3. 取扩展事件文件中的数据并插入到表中
	;WITH events_cte AS (
		SELECT DATEADD(
				mi,
				DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
				xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')
			) AS [eventTime],
			xevents.event_data.value('(event/@name)[1]', 'nvarchar(128)') AS 
			[eventName],
			/*
			xevents.event_data.value('(event/action[@name="client_app_name"]/value)[1]',
			'nvarchar(128)') AS [clientAppName] ,
			xevents.event_data.value('(event/action[@name="client_hostname"]/value)[1]',
			'nvarchar(max)') AS [clientHostName] ,   
			*/
			(
				CASE 
						WHEN xevents.event_data.value(
								'(event/action[@name="sql_text"]/value)[1]',
								'nvarchar(max)'
							) IS NULL THEN xevents.event_data.value(
								'(event/data[@name="statement"]/value)[1]',
								'nvarchar(max)'
							)
						ELSE xevents.event_data.value(
								'(event/action[@name="sql_text"]/value)[1]',
								'nvarchar(max)'
							)
				END
			) AS [statement],
			xevents.event_data.value(
				'(event/action[@name="database_name"]/value)[1]',
				'nvarchar(max)'
			) AS [databaseName],
			xevents.event_data.value(
				'(event/action[@name="username"]/value)[1]',
				'nvarchar(max)'
			) AS [username],
			xevents.event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS 
			[duration],
			xevents.event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') AS 
			[cpu_time],
			xevents.event_data.value('(event/data[@name="physical_reads"]/value)[1]', 'bigint') AS 
			[physical_reads],
			xevents.event_data.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS 
			[logical_reads],
			xevents.event_data.value(
				'(event/data[@name="object_name"]/value)[1]',
				'nvarchar(max)'
			) AS [objectName]
		FROM   sys.fn_xe_file_target_read_file(
				@path,
				NULL,
				NULL,
				NULL
			)
			CROSS APPLY (
			SELECT CAST(event_data AS XML) AS event_data
		) AS xevents
		WHERE  DATEADD(
				mi,
				DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP),
				xevents.event_data.value('(event/@timestamp)[1]', 'datetime2')
			) > @lastTime
	)
	INSERT INTO dbo.DBA_SlowQueryEvent
	(
		eventTime,
		databaseName,
		objectName,
		statement,
		duration,
		cpu_time,
		physical_reads,
		logical_reads,
		eventName,
		username
	)
	SELECT eventTime,
		databaseName,
		objectName,
		statement,
		duration,
		cpu_time,
		physical_reads,
		logical_reads,
		eventName,
		username
	FROM events_cte AS ec	

	--4. 如果是凌晨 2 点，则清除一周前的日志
	IF (DATEPART(hour,GETDATE())=2)
	BEGIN
		DELETE FROM dbo.DBA_SlowQueryEvent
		WHERE eventTime<DATEADD(DAY,-7,GETDATE())
	END
END
GO
EXEC sys.sp_addextendedproperty 
	@name=N'Version', @value=N'1.0' , 
	@level0type=N'SCHEMA',@level0name=N'dbo', 
	@level1type=N'PROCEDURE',@level1name=N'Proc_DBA_GetSlowQueryEvent'

下面的存储过程用于获取表中数据， 标识是否预警：

IF EXISTS (SELECT * FROM   sys.objects WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[Proc_DBA_SlowQueryEventWarning]') AND TYPE IN (N'P', N'PC'))
    DROP PROCEDURE [dbo].Proc_DBA_SlowQueryEventWarning
GO
-- =============================================
-- Author:		yenange
-- Create date: 2017-08-23
-- Description:	慢查询扩展事件预警
-- =============================================
CREATE PROCEDURE [dbo].Proc_DBA_SlowQueryEventWarning
    @warningTimeSeg VARCHAR(100) = '07:30-22:30' --需要预警的时间段, 多个以逗号隔开。默认：07:30-22:30
    ,@seconds INT = 3			--消耗秒数超过多少需要预警
	,@logicreadMB INT = 1024	--逻辑读MB超过多少需要预警
	,@noWarningWords NVARCHAR(500)='Proc_DBA'	--包含哪些内容不需要预警,多个以逗号隔开
AS
BEGIN
	SET NOCOUNT ON;
	------------------- 1.设置时间段是否允许预警 -------------------
	DECLARE @timeSegWarning BIT,@today CHAR(10)
	SET @today= CONVERT(CHAR(10),GETDATE(),120)
	SET @timeSegWarning=0
	DECLARE @seg TABLE (  
		timeSeg CHAR(11), 
		beginSeg DATETIME,
		endSeg DATETIME
	)
	--分隔时间段插入表变量
	INSERT INTO @seg(timeSeg)
	SELECT B.item FROM (
		SELECT [value] = CONVERT(XML,'<v>' + REPLACE( @warningTimeSeg , ',', '</v><v>')+ '</v>')
	) A
	OUTER APPLY(
		SELECT item = N.v.value('.', 'nvarchar(max)') FROM A.[value].nodes('/v') N(v)
	) B
	WHERE  ISNULL(B.item, '') != ''
	UPDATE @seg SET beginSeg = @today+' '+LEFT(timeSeg,5),endSeg = @today + ' ' + RIGHT(timeSeg,5)
	IF EXISTS(SELECT 1 FROM @seg WHERE GETDATE() BETWEEN beginSeg AND endSeg)
	BEGIN
		SET @timeSegWarning=1
	END
	
	------------------- 2.将不需要预警的字符串插入到表变量 -------------------
	DECLARE @noWarningWordTab TABLE ( noWarningWord NVARCHAR(500) )
	INSERT INTO @noWarningWordTab(noWarningWord)
	SELECT B.item FROM (
		SELECT [value] = CONVERT(XML,'<v>' + REPLACE( @noWarningWords , ',', '</v><v>')+ '</v>')
	) A
	OUTER APPLY(
		SELECT item = N.v.value('.', 'nvarchar(max)') FROM A.[value].nodes('/v') N(v)
	) B
	WHERE  ISNULL(B.item, '') != ''
	
	--3. 定义表变量并插入未获取过的数据
	DECLARE @t TABLE(
	    Warning BIT,
		[eventTime] [datetime],
		[databaseName] [nvarchar](max),
		[objectName] [nvarchar](max),
		[statement] [nvarchar](max),
		[elapsedSeconds]  [decimal](15,2),
		[cpuSeconds]  [decimal](15,2),
		[logical_reads_MB]  BIGINT,
		[physical_reads_MB] BIGINT,
		[eventName] [nvarchar](128) NULL,
		[username] [nvarchar](max) NULL,
		[eventId] BIGINT PRIMARY KEY
	)
 	INSERT INTO @t
	 (
	    Warning,
		[eventTime],
		[databaseName],
		[objectName],
		[statement],
		[elapsedSeconds],
		[cpuSeconds],
		[logical_reads_MB],
		[physical_reads_MB],
		[eventName],
		[username],
		[eventId]
	  )
	SELECT CASE 
	            WHEN @timeSegWarning=1 AND [elapsedSeconds] >= @seconds THEN 1
	            WHEN @timeSegWarning=1 AND [logical_reads_MB] > @logicreadMB THEN 1
	            ELSE 0
	       END  AS	Warning,
	    [eventTime],
		[databaseName],
		[objectName],
		[statement],
		[elapsedSeconds],
		[cpuSeconds],
		[logical_reads_MB],
		[physical_reads_MB],
		[eventName],
		[username],
		[eventId]
	FROM [dbo].[DBA_SlowQueryEvent]
	WHERE [checked]=0
	ORDER BY eventTime DESC
	
	--将包含不需要预警的关键字的记录改为 Warning = 0
	UPDATE t SET Warning = 0
	FROM @t AS t, @noWarningWordTab AS A
	WHERE Warning=1 AND t.[statement] LIKE '%'+a.noWarningWord+'%'
	
	--将原表中对应的数据改为已获取
	UPDATE [dbo].[DBA_SlowQueryEvent] SET checked = 1 
	FROM @t AS t
	WHERE [DBA_SlowQueryEvent].eventId = t.eventId
	
	--输出信息
	SELECT Warning,
	    [eventTime],
		[databaseName],
		[objectName],
		[statement],
		[elapsedSeconds],
		[cpuSeconds],
		[logical_reads_MB],
		[physical_reads_MB],
		[eventName],
		[username]
	FROM @t
	
	SET NOCOUNT OFF
END
GO
EXEC sys.sp_addextendedproperty 
	@name=N'Version', @value=N'1.0'
	, @level0type=N'SCHEMA',@level0name=N'dbo'
	, @level1type=N'PROCEDURE',@level1name=N'Proc_DBA_SlowQueryEventWarning'