如何检测并避免错误关联查询导致数据库CPU100%占用

代码层面（C# + ADO.NET） #

1. 解析SQL语句检测无效关联 #

使用微软官方的Microsoft.SqlServer.TransactSql.ScriptDom库解析SQL抽象语法树（AST），自动检测缺失关联条件的JOIN或潜在笛卡尔积：

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Collections.Generic;
using System.IO;
using System.Xml;

public bool HasInvalidJoin(string sql)
{
    var parser = new TSql160Parser(false);
    using var reader = new StringReader(sql);
    var errors = new List<ParseError>();
    var script = parser.Parse(reader, out errors);
    
    if (errors.Any()) return false; // 先排除语法错误
    
    var visitor = new JoinValidatorVisitor();
    script.Accept(visitor);
    return visitor.HasInvalidJoin;
}

public class JoinValidatorVisitor : TSqlFragmentVisitor
{
    public bool HasInvalidJoin { get; private set; }

    public override void ExplicitVisit(JoinTableReference node)
    {
        // 检查非显式CROSS JOIN的语句是否遗漏ON条件
        if (node.JoinDefinition?.SearchCondition == null && 
            node.JoinType != JoinType.CrossJoin)
        {
            HasInvalidJoin = true;
        }
        base.ExplicitVisit(node);
    }
}

检测到无效关联时，直接拒绝执行该查询。

2. 预获取执行计划评估风险 #

执行实际查询前，先通过SET SHOWPLAN_XML ON获取查询计划，解析是否存在笛卡尔积或预估行数超标的情况：

public bool IsQueryRisky(string sql, SqlConnection connection)
{
    using var cmd = new SqlCommand("SET SHOWPLAN_XML ON; " + sql, connection);
    cmd.CommandTimeout = 10; // 限制计划生成时间
    
    using var reader = cmd.ExecuteReader();
    if (reader.Read() && reader[0] is XmlDocument planXml)
    {
        // 检测笛卡尔积节点
        var crossJoins = planXml.SelectNodes("//*[@LogicalOp='Cross Join']");
        if (crossJoins != null && crossJoins.Count > 0)
        {
            return true;
        }
        // 检测预估行数是否超过阈值（示例为100万）
        var estimatedRows = planXml.SelectNodes("//*[@EstimatedRows]");
        foreach (XmlNode node in estimatedRows)
        {
            if (double.Parse(node.Attributes["EstimatedRows"].Value) > 1000000)
            {
                return true;
            }
        }
    }
    return false;
}

标记为风险的查询直接拦截，不执行。

3. 实现查询白名单/模板机制 #

预先定义允许的表关联规则（比如Orders仅能通过CustomerId关联Customers），用户查询必须匹配规则才能执行。可以通过AST比对或正则表达式验证，强制查询包含合法关联条件。

数据库层面 #

1. 启用资源调控器（以SQL Server为例） #

配置Resource Governor，给用户查询分配固定CPU资源上限，避免单个查询耗尽CPU：

-- 创建资源池，限制CPU使用率为30%
CREATE RESOURCE POOL UserQueryPool WITH (MAX_CPU_PERCENT = 30);
GO
-- 创建绑定到资源池的工作负载组
CREATE WORKLOAD GROUP UserQueries USING UserQueryPool;
GO
-- 启用资源调控器
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- 定义分类函数，将指定用户的连接映射到工作负载组
CREATE FUNCTION dbo.UserQueryClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    IF SUSER_SNAME() = 'EndUserLogin'
        RETURN 'UserQueries';
    RETURN 'default';
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserQueryClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

2. 设置查询超时与返回行数限制 #

在数据库层面全局或针对会话设置查询超时，避免长时间运行的查询占用资源：

-- 全局设置查询超时（单位：秒）
sp_configure 'remote query timeout', 300; -- 5分钟
RECONFIGURE;
GO
-- 针对当前会话设置
SET QUERY_TIMEOUT 300;

同时限制查询最大返回行数，截断超大结果集：

SET ROWCOUNT 10000; -- 最多返回1万行

3. 监控并拦截危险查询 #

使用扩展事件监控包含笛卡尔积的查询，配合SQL Server Agent作业自动终止触发的会话：

-- 创建扩展事件会话
CREATE EVENT SESSION BlockCrossJoins ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.session_id)
    WHERE (xml_data.value('(//@LogicalOp)[1]', 'nvarchar(100)') = 'Cross Join')
)
ADD TARGET package0.event_file(SET filename=N'BlockCrossJoins.xel')
WITH (STARTUP_STATE=ON);
GO

定期扫描事件日志并终止危险会话：

DECLARE @sessionId INT;
SELECT @sessionId = CAST(xdata.value('(event/action[@name="session_id"]/value)[1]', 'int') AS INT)
FROM sys.fn_xe_file_target_read_file('BlockCrossJoins*.xel', NULL, NULL, NULL)
WHERE xdata.value('(event/data[@name="xml_data"]/value/query_plan//@LogicalOp)[1]', 'nvarchar(100)') = 'Cross Join';

IF @sessionId IS NOT NULL
BEGIN
    KILL @sessionId;
END;

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