建立慢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'