如何排查数据库中特定表的查询语句（排除Delete）以确定索引列？

要筛选针对特定表的SELECT查询（排除DELETE等写操作）来分析索引需求，原SQL的核心问题是通过文本模糊匹配会误命中包含表名的DELETE语句，且无法精准区分语句类型。以下是两种优化方案：

方案一：精准匹配（推荐） #

利用执行计划的XML元数据（XQuery）精准识别SELECT语句和目标表引用，避免文本匹配的误差：

SELECT 
    qs.sql_handle,
    -- 提取执行的具体语句片段
    SUBSTRING(
        st.text, 
        (qs.statement_start_offset / 2) + 1, 
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE qs.statement_end_offset 
        END - qs.statement_start_offset) / 2) + 1) AS query_text,
    qp.query_plan,
    -- 显式返回语句类型，便于验证
    qp.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
        (/p:ShowPlanXML/p:BatchSequence/p:Batch/p:Statements/p:StmtSimple/@StatementType)[1]', 'nvarchar(100)') AS statement_type
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE 
    -- 仅保留SELECT类型语句
    qp.query_plan.exist('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
        /p:ShowPlanXML/p:BatchSequence/p:Batch/p:Statements/p:StmtSimple[@StatementType="SELECT"]') = 1
    -- 精准匹配目标表（替换为实际的数据库、架构、表名）
    AND qp.query_plan.exist('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 
        //p:Object[@Database="[YourDatabaseName]" and @Schema="[YourSchemaName]" and @Table="[YourTableName]"]') = 1
    -- 排除系统内部查询
    AND st.is_system = 0
ORDER BY 
    qs.total_worker_time DESC;

核心优化点： #

• 通过执行计划XML的StatementType属性精准筛选SELECT语句，不受批处理或文本内容干扰
• 匹配目标表的完整数据库/架构/表名，彻底避免注释、字段值等无关文本误命中
• 排除系统查询，仅分析用户发起的业务查询

方案二：简化文本过滤（快速验证） #

如果暂时无法使用XQuery，可通过文本过滤快速排除写操作，适合临时验证：

SELECT 
    qs.sql_handle,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 
    ((CASE qs.statement_end_offset 
      WHEN -1 THEN DATALENGTH(st.text) 
      ELSE qs.statement_end_offset 
      END - qs.statement_start_offset) / 2) + 1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE 
    -- 排除DELETE/INSERT/UPDATE等写操作（注意大小写，可根据数据库排序规则调整）
    st.text NOT LIKE '%DELETE%'
    AND st.text NOT LIKE '%INSERT%'
    AND st.text NOT LIKE '%UPDATE%'
    -- 匹配目标表
    AND st.text LIKE '%YourTableName%'
    -- 排除系统查询
    AND st.is_system = 0
ORDER BY 
    qs.total_worker_time DESC;

局限性： #

• 若SELECT语句中包含DELETE/INSERT等字符串（如字段值、注释），会被误过滤
• 若DELETE语句的注释中包含目标表名，会被误命中

使用提示： #

1. 替换占位符[YourDatabaseName]、[YourSchemaName]、[YourTableName]为实际信息
2. 若需包含SELECT INTO语句，修改方案一的XQuery条件为@StatementType="SELECT" or @StatementType="SELECT INTO"
3. 可结合执行计划中的MissingIndex节点，直接提取建议创建的索引，提升分析效率

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