利用 trace 监控慢查询

  • 第1步，创建存储过程 master..dba_trace_slowquery 用于定义 trace 监控指标

注：3 天后停止监控、每个日志文件最大1GB、最多5个(循环覆盖,最多占用5G空间)，慢查询定义：耗时>=3秒 或 Reads >= 40000 或 CPU > 1000

USE master 
GO 

IF OBJECT_ID('dba_trace_slowquery') IS NOT NULL 
DROP PROCEDURE dba_trace_slowquery 
GO 

CREATE PROC dba_trace_slowquery 
  @tracepath NVARCHAR(200) 
AS

/*************************************************************************************** 
Funtion : 跟踪RPC:Completed、SQL:BatchCompleted
Input   ：@tracepath    输入  设置跟踪文件的存放目录. 
Author  : dba_yuxiukai  2015/12/15  新增  编写草稿. 
Sample  : exec master..dba_trace_slowquery @tracepath='d:\sqldata\trace'
***************************************************************************************/  

BEGIN 
DECLARE @oldtrace INT 
DECLARE @mdir NVARCHAR(200) 
DECLARE @rc INT 
DECLARE @traceid INT 
DECLARE @options INT 
DECLARE @maxfilesize BIGINT 
DECLARE @endtime DATETIME 
DECLARE @filecount INT 
DECLARE @tracefile NVARCHAR(200) 
DECLARE @BigIntFilter BIGINT 
DECLARE @on BIT 

SET @on = 1 
SET @options = 2 
SET @filecount = 5            --.最多5个日志文件
SET @maxfilesize = 1024       --.单个文件最大1GB
SET @endtime = GETDATE() + 3  --.默认3天后停止监控

--Create the folder.
SET @mdir = N'EXECUTE master.dbo.xp_create_subdir ''' + @tracepath + '''' 
EXEC sp_executesql @mdir 

--Stop old trace.
SELECT @oldtrace = id FROM sys.traces WHERE path LIKE @tracepath +'\dba_trace_slowquery%' AND status = 1 
IF @oldtrace IS NOT NULL 
BEGIN 
    EXEC sp_trace_setstatus @oldtrace, 0 
    EXEC sp_trace_setstatus @oldtrace, 2 
END 

--Create new trace.
SET @tracefile = @tracepath+'\dba_trace_slowquery' + CONVERT(VARCHAR(8),GETDATE(),112) + LEFT(NEWID(),4) 
EXEC @rc = sp_trace_Create @traceid OUTPUT,@options,@tracefile,@maxfilesize,@endtime,@filecount 
IF (@rc != 0) GOTO error 

--Set the events 
EXEC sp_trace_setevent @traceid, 10, 1,  @on  --event_number(RPC:Completed) 
EXEC sp_trace_setevent @traceid, 10, 3,  @on  
EXEC sp_trace_setevent @traceid, 10, 8,  @on  
EXEC sp_trace_setevent @traceid, 10, 10, @on  
EXEC sp_trace_setevent @traceid, 10, 11, @on  
EXEC sp_trace_setevent @traceid, 10, 12, @on  
EXEC sp_trace_setevent @traceid, 10, 13, @on  
EXEC sp_trace_setevent @traceid, 10, 14, @on  
EXEC sp_trace_setevent @traceid, 10, 15, @on  
EXEC sp_trace_setevent @traceid, 10, 16, @on  
EXEC sp_trace_setevent @traceid, 10, 17, @on  
EXEC sp_trace_setevent @traceid, 10, 18, @on  
EXEC sp_trace_setevent @traceid, 10, 26, @on  
EXEC sp_trace_setevent @traceid, 10, 31, @on  
EXEC sp_trace_setevent @traceid, 10, 34, @on  
EXEC sp_trace_setevent @traceid, 10, 35, @on  
EXEC sp_trace_setevent @traceid, 10, 41, @on  
EXEC sp_trace_setevent @traceid, 12, 1,  @on  --event_number(SQL:BatchCompleted) 
EXEC sp_trace_setevent @traceid, 12, 3,  @on  
EXEC sp_trace_setevent @traceid, 12, 8,  @on  
EXEC sp_trace_setevent @traceid, 12, 10, @on  
EXEC sp_trace_setevent @traceid, 12, 11, @on  
EXEC sp_trace_setevent @traceid, 12, 12, @on  
EXEC sp_trace_setevent @traceid, 12, 13, @on  
EXEC sp_trace_setevent @traceid, 12, 14, @on  
EXEC sp_trace_setevent @traceid, 12, 15, @on  
EXEC sp_trace_setevent @traceid, 12, 16, @on  
EXEC sp_trace_setevent @traceid, 12, 17, @on  
EXEC sp_trace_setevent @traceid, 12, 18, @on  
EXEC sp_trace_setevent @traceid, 12, 26, @on  
EXEC sp_trace_setevent @traceid, 12, 31, @on  
EXEC sp_trace_setevent @traceid, 12, 34, @on  
EXEC sp_trace_setevent @traceid, 12, 35, @on  
EXEC sp_trace_setevent @traceid, 12, 41, @on  
EXEC sp_trace_setevent @traceid, 92, 1,  @on  --event_number(Data File Auto Grow) 
EXEC sp_trace_setevent @traceid, 92, 3,  @on  
EXEC sp_trace_setevent @traceid, 92, 4,  @on  
EXEC sp_trace_setevent @traceid, 92, 11, @on  
EXEC sp_trace_setevent @traceid, 92, 12, @on  
EXEC sp_trace_setevent @traceid, 92, 13, @on  
EXEC sp_trace_setevent @traceid, 92, 14, @on  
EXEC sp_trace_setevent @traceid, 92, 15, @on  
EXEC sp_trace_setevent @traceid, 92, 22, @on  
EXEC sp_trace_setevent @traceid, 92, 24, @on  
EXEC sp_trace_setevent @traceid, 92, 25, @on  
EXEC sp_trace_setevent @traceid, 92, 26, @on  
EXEC sp_trace_setevent @traceid, 92, 32, @on  
EXEC sp_trace_setevent @traceid, 92, 35, @on  
EXEC sp_trace_setevent @traceid, 92, 36, @on  
EXEC sp_trace_setevent @traceid, 92, 41, @on  
EXEC sp_trace_setevent @traceid, 93, 1,  @on  --event_number(Log File Auto Grow) 
EXEC sp_trace_setevent @traceid, 93, 3,  @on  
EXEC sp_trace_setevent @traceid, 93, 4,  @on  
EXEC sp_trace_setevent @traceid, 93, 11, @on  
EXEC sp_trace_setevent @traceid, 93, 12, @on  
EXEC sp_trace_setevent @traceid, 93, 13, @on  
EXEC sp_trace_setevent @traceid, 93, 14, @on  
EXEC sp_trace_setevent @traceid, 93, 15, @on  
EXEC sp_trace_setevent @traceid, 93, 22, @on  
EXEC sp_trace_setevent @traceid, 93, 24, @on  
EXEC sp_trace_setevent @traceid, 93, 25, @on  
EXEC sp_trace_setevent @traceid, 93, 26, @on  
EXEC sp_trace_setevent @traceid, 93, 32, @on  
EXEC sp_trace_setevent @traceid, 93, 35, @on  
EXEC sp_trace_setevent @traceid, 93, 36, @on  
EXEC sp_trace_setevent @traceid, 93, 41, @on  
EXEC sp_trace_setevent @traceid, 94, 1,  @on  --event_number(Data File Auto Shrink) 
EXEC sp_trace_setevent @traceid, 94, 3,  @on  
EXEC sp_trace_setevent @traceid, 94, 4,  @on  
EXEC sp_trace_setevent @traceid, 94, 11, @on  
EXEC sp_trace_setevent @traceid, 94, 12, @on  
EXEC sp_trace_setevent @traceid, 94, 13, @on  
EXEC sp_trace_setevent @traceid, 94, 14, @on  
EXEC sp_trace_setevent @traceid, 94, 15, @on  
EXEC sp_trace_setevent @traceid, 94, 22, @on  
EXEC sp_trace_setevent @traceid, 94, 24, @on  
EXEC sp_trace_setevent @traceid, 94, 25, @on  
EXEC sp_trace_setevent @traceid, 94, 26, @on  
EXEC sp_trace_setevent @traceid, 94, 32, @on  
EXEC sp_trace_setevent @traceid, 94, 35, @on  
EXEC sp_trace_setevent @traceid, 94, 36, @on  
EXEC sp_trace_setevent @traceid, 94, 41, @on  
EXEC sp_trace_setevent @traceid, 95, 1,  @on  --event_number(Log File Auto Shrink) 
EXEC sp_trace_setevent @traceid, 95, 3,  @on  
EXEC sp_trace_setevent @traceid, 95, 4,  @on  
EXEC sp_trace_setevent @traceid, 95, 11, @on  
EXEC sp_trace_setevent @traceid, 95, 12, @on  
EXEC sp_trace_setevent @traceid, 95, 13, @on  
EXEC sp_trace_setevent @traceid, 95, 14, @on  
EXEC sp_trace_setevent @traceid, 95, 15, @on  
EXEC sp_trace_setevent @traceid, 95, 22, @on  
EXEC sp_trace_setevent @traceid, 95, 24, @on  
EXEC sp_trace_setevent @traceid, 95, 25, @on  
EXEC sp_trace_setevent @traceid, 95, 26, @on  
EXEC sp_trace_setevent @traceid, 95, 32, @on  
EXEC sp_trace_setevent @traceid, 95, 35, @on  
EXEC sp_trace_setevent @traceid, 95, 36, @on  
EXEC sp_trace_setevent @traceid, 95, 41, @on  

--Set the Filters 
EXEC sp_trace_setfilter @traceid, 1, 0, 1, NULL 
EXEC sp_trace_setfilter @traceid = @traceid, @columnid = 10, @logical_operator = 0, @comparison_operator = 7, @value = N'SQL Profiler%'    --AND ApplicationName NOT LIKE N'SQL Profiler%' 
SET @BigIntFilter = 3000000 
EXEC sp_trace_setfilter @traceid = @traceid, @columnid = 13, @logical_operator = 0, @comparison_operator = 4, @value = @BigIntFilter       --AND Duration >= 3000000 
SET @BigIntFilter = 40000 
EXEC sp_trace_setfilter @traceid = @traceid, @columnid = 16, @logical_operator = 1, @comparison_operator = 4, @value = @BigIntFilter       --OR Reads >= 40000 
EXEC sp_trace_setfilter @traceid = @traceid, @columnid = 18, @logical_operator = 1, @comparison_operator = 2, @value = 1000                --OR CPU > 1000 

--Set the trace status to start 
EXEC sp_trace_setstatus @traceid, 1 

--display trace id for future references 
SELECT traceid = @traceid 
GOTO finish 

error: 
SELECT ErrorCode = @rc 

finish: 
END 
GO
  • 第2步，开启 trace 监控，可自定义日志路径，示例 d:\trace

注：若运行期间 SQLServer 服务重启，则需重手动再次开启

exec master..dba_trace_slowquery @tracepath='d:\trace'
  • 第3步，开启监控后，根据返回的 trace id 查看具体的慢查询记录

注：开启 Trace 服务之后通常有一个 trace 为 1 的默认监控，以下示例查询的监控是 id = 2

--获取默认跟踪文件中前n行执行内容
DECLARE @TracePath VARCHAR(200) 
SELECT @TracePath = path FROM sys.traces WHERE id = 2
-- SELECT * FROM sys.traces

SELECT TOP 100 
   gt.[HostName] 
  ,gt.[DatabaseName] 
  ,gt.[SPID] 
  ,gt.[ObjectName]
  ,gt.[objecttype] [ObjectTypeID] 
  ,gt.[Duration]/1000 [Duration_ms]
  ,gt.[StartTime] 
  ,gt.[Reads] [Reads]
  ,gt.[Writes] [Writes]
  ,gt.[CPU] [CPU]
  ,e.[category_id] [CategoryID] 
  ,c.[Name] [Category] 
  ,gt.[EventClass] [EventID] 
  ,e.[Name] [EventName] 
  ,gt.[LoginName] 
  ,gt.[ApplicationName] 
  ,case gt.Error when 0 then 'OK' when 1 then 'Error' when 2 then 'Abort' END AS Error 
  ,gt.[TextData] 
FROM fn_trace_gettable(@TracePath, DEFAULT) gt 
LEFT JOIN sys.trace_subclass_values sv ON gt.[eventclass] = sv.[trace_event_id] AND sv.[subclass_value] = gt.[objecttype] 
INNER JOIN sys.trace_events e ON gt.[eventclass] = e.[trace_event_id] 
INNER JOIN sys.trace_categories c ON e.[category_id] = c.[category_id] 
WHERE gt.[spid] > 50    --50以内的spid为系统使用 
  --AND gt.loginname <> 'dba_admin'
  --AND gt.[StartTime] > DATEADD(MINUTE,-30,GETDATE()) 
  --AND gt.[DatabaseName] = 'master'    --根据DatabaseName过滤 
ORDER BY gt.[Duration] DESC
  • 第4步，若要提前停止监控，可根据 trace id 来进行关闭，示例 id = 2
--.执行如下语句找出trace监控的id
select id,* from sys.traces

--.根据id停止跟踪,示例停止id为2的监控
declare @id int = 2
exec sp_trace_setstatus @id,0 
exec sp_trace_setstatus @id,2
Copyright © www.sqlfans.cn 2021 - 2026 All Right Reserved更新时间： 2026-08-30 09:07:05

results matching ""

    No results matching ""