将扩展事件（慢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_GetSlowQueryEventGO-- =============================================-- Author:yenange-- Create date: 2017-08-23-- Description:获取慢查询扩展事件的内容并插入到表-- =============================================CREATE PROCEDURE [dbo].Proc_DBA_GetSlowQueryEventASBEGINSET NOCOUNT ON;--0. 如果不存在则创建表IF OBJECT_ID('dbo.DBA_SlowQueryEvent') IS NULLBEGINCREATE 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] aleft join [sys].[dm_xe_session_targets] b on a.[address]=b.event_session_addressWHERE a.name='slow_query'--2. 获取上次的获取的最后的时间，如果没有记录，取最近30分钟DECLARE @lastTime DATETIME2SELECT @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 xeventsWHERE  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,usernameFROM events_cte AS ec--4. 如果是凌晨 2 点，则清除一周前的日志IF (DATEPART(hour,GETDATE())=2)BEGINDELETE FROM dbo.DBA_SlowQueryEventWHERE eventTime<DATEADD(DAY,-7,GETDATE())ENDENDGOEXEC 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_SlowQueryEventWarningGO-- =============================================-- 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'--包含哪些内容不需要预警,多个以逗号隔开ASBEGINSET NOCOUNT ON;------------------- 1.设置时间段是否允许预警 -------------------DECLARE @timeSegWarning BIT,@today CHAR(10)SET @today= CONVERT(CHAR(10),GETDATE(),120)SET @timeSegWarning=0DECLARE @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>')) AOUTER APPLY(SELECT item = N.v.value('.', 'nvarchar(max)') FROM A.[value].nodes('/v') N(v)) BWHERE  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)BEGINSET @timeSegWarning=1END------------------- 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>')) AOUTER APPLY(SELECT item = N.v.value('.', 'nvarchar(max)') FROM A.[value].nodes('/v') N(v)) BWHERE  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  ASWarning,    [eventTime],[databaseName],[objectName],[statement],[elapsedSeconds],[cpuSeconds],[logical_reads_MB],[physical_reads_MB],[eventName],[username],[eventId]FROM [dbo].[DBA_SlowQueryEvent]WHERE [checked]=0ORDER BY eventTime DESC--将包含不需要预警的关键字的记录改为 Warning = 0UPDATE t SET Warning = 0FROM @t AS t, @noWarningWordTab AS AWHERE Warning=1 AND t.[statement] LIKE '%'+a.noWarningWord+'%'--将原表中对应的数据改为已获取UPDATE [dbo].[DBA_SlowQueryEvent] SET checked = 1 FROM @t AS tWHERE [DBA_SlowQueryEvent].eventId = t.eventId--输出信息SELECT Warning,    [eventTime],[databaseName],[objectName],[statement],[elapsedSeconds],[cpuSeconds],[logical_reads_MB],[physical_reads_MB],[eventName],[username]FROM @tSET NOCOUNT OFFENDGOEXEC sys.sp_addextendedproperty @name=N'Version', @value=N'1.0', @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'Proc_DBA_SlowQueryEventWarning'