dbWarden长查询监控：如何用变量在WHERE子句实现存储过程排除？

架构老林

2026-7-2

问题描述

我正在使用dbWarden进行SQL监控，希望基于排除列表表将特定存储过程从“长运行查询”任务中排除。我尝试在WHERE子句中排除表中列出的一系列SQL_TEXT字符串。

我编辑的存储过程部分如下：

DECLARE @exclusions NVARCHAR (MAX);
SELECT @exclusions = COALESCE(@exclusions + '%''' +  ' and SQL_TEXT not like ''%' + SQL_TEXT , SQL_TEXT) FROM LongRunningQueriesExclusions 

SELECT * FROM QUERYHISTORY
                WHERE RunTime >= 10
                AND [DBName] NOT IN (SELECT [DBName] COLLATE DATABASE_DEFAULT FROM [DBATools].dbo.DatabaseSettings WHERE LongQueryAlerts = 0)
                AND Formatted_SQL_Text NOT LIKE '%BACKUP DATABASE%'
                AND Formatted_SQL_Text NOT LIKE '%RESTORE VERIFYONLY%'
                AND Formatted_SQL_Text NOT LIKE '%ALTER INDEX%'
                AND Formatted_SQL_Text NOT LIKE '%DECLARE @BlobEater%'
                AND Formatted_SQL_Text NOT LIKE '%DBCC%'
                AND Formatted_SQL_Text NOT LIKE '%FETCH API_CURSOR%'
                AND Formatted_SQL_Text NOT LIKE '%WAITFOR(RECEIVE%'
                -- 我的新增部分 ------------------------------------------------------
                AND SQL_TEXT not like  '''%' + @exclusions + '%'''

我的排除表包含需要排除的存储过程，打印@exclusions变量时得到：

SP_testProc%' and SQL_TEXT not like '%SP_AnotherTestProc%' and SQL_TEXT not like '%SP_OneMoreTest

将变量替换为打印的文本时查询结果符合预期，但直接使用变量却无法正常工作。请问是否有办法在查询的WHERE子句中包含带通配符的排除列表？

问题原因

你当前的写法错误在于：SQL Server会把@exclusions变量中的整个字符串当成单一的LIKE匹配值，而不是解析成多个AND SQL_TEXT NOT LIKE逻辑条件。比如最终执行的是SQL_TEXT not like '%SP_testProc%' and SQL_TEXT not like '%SP_AnotherTestProc%' and SQL_TEXT not like '%SP_OneMoreTest%'，这里的后半段都是LIKE的匹配字符串，而非逻辑条件，所以无法生效。

解决方案

推荐两种可行的实现方式：

方式一：使用NOT EXISTS关联排除表（推荐，无需动态SQL）

直接通过关联排除表，检查当前查询的SQL_TEXT是否匹配任何排除项，逻辑更清晰且避免动态SQL的风险：

SELECT * FROM QUERYHISTORY qh
WHERE RunTime >= 10
AND [DBName] NOT IN (SELECT [DBName] COLLATE DATABASE_DEFAULT FROM [DBATools].dbo.DatabaseSettings WHERE LongQueryAlerts = 0)
AND Formatted_SQL_Text NOT LIKE '%BACKUP DATABASE%'
AND Formatted_SQL_Text NOT LIKE '%RESTORE VERIFYONLY%'
AND Formatted_SQL_Text NOT LIKE '%ALTER INDEX%'
AND Formatted_SQL_Text NOT LIKE '%DECLARE @BlobEater%'
AND Formatted_SQL_Text NOT LIKE '%DBCC%'
AND Formatted_SQL_Text NOT LIKE '%FETCH API_CURSOR%'
AND Formatted_SQL_Text NOT LIKE '%WAITFOR(RECEIVE%'
-- 替换原有变量拼接逻辑，用NOT EXISTS实现排除
AND NOT EXISTS (
    SELECT 1 
    FROM LongRunningQueriesExclusions lqe
    WHERE qh.SQL_TEXT LIKE '%' + lqe.SQL_TEXT + '%'
)

方式二：使用动态SQL（适合必须拼接逻辑的场景）

如果一定要通过拼接条件字符串实现，需要将整个查询构造成动态SQL执行，让SQL Server解析变量中的逻辑条件：

DECLARE @exclusions NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 正确拼接排除条件的逻辑语句
SELECT @exclusions = COALESCE(@exclusions + ' AND SQL_TEXT NOT LIKE ''%' + SQL_TEXT + '%''', 'SQL_TEXT NOT LIKE ''%' + SQL_TEXT + '%''') 
FROM LongRunningQueriesExclusions;

-- 拼接完整查询语句
SET @sql = N'
SELECT * FROM QUERYHISTORY
WHERE RunTime >= 10
AND [DBName] NOT IN (SELECT [DBName] COLLATE DATABASE_DEFAULT FROM [DBATools].dbo.DatabaseSettings WHERE LongQueryAlerts = 0)
AND Formatted_SQL_Text NOT LIKE ''%BACKUP DATABASE%''
AND Formatted_SQL_Text NOT LIKE ''%RESTORE VERIFYONLY%''
AND Formatted_SQL_Text NOT LIKE ''%ALTER INDEX%''
AND Formatted_SQL_Text NOT LIKE ''%DECLARE @BlobEater%''
AND Formatted_SQL_Text NOT LIKE ''%DBCC%''
AND Formatted_SQL_Text NOT LIKE ''%FETCH API_CURSOR%''
AND Formatted_SQL_Text NOT LIKE ''%WAITFOR(RECEIVE%''
' + CASE WHEN @exclusions IS NOT NULL THEN ' AND ' + @exclusions ELSE '' END;

-- 执行动态SQL
EXEC sp_executesql @sql;

方式三：使用STRING_AGG（SQL Server 2017及以上版本）

如果你的SQL Server版本支持STRING_AGG函数，可以更简洁地拼接排除条件：

DECLARE @exclusions NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 用STRING_AGG直接拼接所有排除条件
SELECT @exclusions = STRING_AGG('SQL_TEXT NOT LIKE ''%' + SQL_TEXT + '%''', ' AND ')
FROM LongRunningQueriesExclusions;

-- 拼接并执行动态SQL
SET @sql = N'
SELECT * FROM QUERYHISTORY
WHERE RunTime >= 10
AND [DBName] NOT IN (SELECT [DBName] COLLATE DATABASE_DEFAULT FROM [DBATools].dbo.DatabaseSettings WHERE LongQueryAlerts = 0)
AND Formatted_SQL_Text NOT LIKE ''%BACKUP DATABASE%''
AND Formatted_SQL_Text NOT LIKE ''%RESTORE VERIFYONLY%''
AND Formatted_SQL_Text NOT LIKE ''%ALTER INDEX%''
AND Formatted_SQL_Text NOT LIKE ''%DECLARE @BlobEater%''
AND Formatted_SQL_Text NOT LIKE ''%DBCC%''
AND Formatted_SQL_Text NOT LIKE ''%FETCH API_CURSOR%''
AND Formatted_SQL_Text NOT LIKE ''%WAITFOR(RECEIVE%''
' + CASE WHEN @exclusions IS NOT NULL THEN ' AND ' + @exclusions ELSE '' END;

EXEC sp_executesql @sql;

内容的提问来源于stack exchange，提问作者FaatFire