SQLServer - TOP 事件查询

1. 锁等待：“为什么我的查询一直卡着不动？”（阻塞）

在真实的锁等待排查中，两条 SQL 常搭配使用，流程如下：
    1. 先执行第一条 SQL：快速查看全局请求状态，确认是否存在大量blocking_session_id <> 0的请求（即锁阻塞确实存在），并初步判断影响范围；
    2. 再执行第二条 SQL：聚焦被阻塞会话，获取 “阻塞者 ID + 阻塞者 SQL”，定位根源语句；
    3. 若需进一步定位阻塞者的客户端（如是谁发起的阻塞 SQL），可通过第二条 SQL 得到的blocking_session_id，再用第一条 SQL 的关联逻辑查询客户端地址（client_net_address）。

1.1 初步排查：数据库卡顿，不确定是否是锁阻塞

    • 数据源与关联逻辑：
      主表是sys.dm_exec_requests（当前所有执行中 / 等待中的请求），关联sys.dm_exec_connections获取客户端 IP（client_net_address）、本地地址，关联sys.dm_exec_sessions过滤 “用户进程”（排除系统进程），通过OUTER APPLY获取请求对应的 SQL 语句。
    • 核心特点：
      • 无blocking_session_id <> 0筛选，因此结果会包含正常运行的请求（blocking_session_id = 0）和被阻塞的请求（blocking_session_id <> 0），属于 “全局视图”。
      • 输出信息极全：除锁等待相关字段（blocking_session_id、wait_type、wait_resource），还包含请求进度（percent_complete，适合长操作如备份 / 还原）、网络地址（定位卡顿客户端）、CPU/IO 次数（辅助判断性能瓶颈）。
    • 局限性：
      仅能看到 “被阻塞会话的阻塞者 ID”（blocking_session_id），但无法直接获取阻塞者正在执行的 SQL—— 若要查阻塞者的 SQL，需额外执行查询（如通过blocking_session_id关联sys.dm_exec_requests）。
关键维度技术细节
数据来源（DMV） - sys.dm_exec_requests：当前正在执行的请求（含等待状态的请求）
- sys.dm_exec_connections：会话的网络连接信息（客户端 IP、本地 IP）
- sys.dm_exec_sessions：会话基本信息（过滤系统进程）
数据时效性 实时活跃数据：仅包含「当前正在运行 / 等待」的用户会话（排除系统进程 is_user_process=1），会话结束后数据消失。
核心关注指标 - blocking_session_id：阻塞当前会话的 “元凶” 会话 ID（关键！定位阻塞源）
- wait_type：等待类型（如 LCK_M_X 排它锁等待、LCK_M_S 共享锁等待）
- wait_resource：等待的具体资源（如某表的某行数据）
- wait_time：等待时长
过滤逻辑 排除系统进程（is_user_process=1），确保只看用户业务导致的阻塞。 
SELECT  
der.[session_id],
der.[request_id],
der.percent_complete,
der.estimated_completion_time/1000/60,
con.[client_net_address],
con.local_net_address,
der.[start_time] AS '开始时间',
der.[status] AS '状态',
der.[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME(der.[database_id]) AS '数据库名',
der.[blocking_session_id] AS '正在阻塞其他会话的会话ID',
der.[wait_type] AS '等待资源类型',
der.[wait_time] AS '等待时间',
der.[wait_resource] AS '等待的资源',
der.[reads] AS '物理读次数',
der.[writes] AS '写次数',
der.[logical_reads] AS '逻辑读次数',
der.[row_count] AS '返回结果行数',
der.[cpu_time],
der.percent_complete
FROM sys.[dm_exec_requests] AS der
outer APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
left join sys.dm_exec_connections con
on der.session_id=con.session_id
left join sys.dm_exec_sessions es
on es.session_id=con.session_id
WHERE es.[is_user_process] = 1
--AND DB_NAME([database_id])='UserSystemBusiness'
ORDER BY der.[reads] DESC

1.2 精准定位：已知有锁等待，需快速解决阻塞

    • 数据源与关联逻辑：
      主表仍是sys.dm_exec_requests，但通过WHERE blocking_session_id <> 0精准筛选 “被阻塞的请求”；通过GROUP BY按 “阻塞者 ID、数据库、SQL 文本” 分组，统计阻塞次数（COUNT(*)）和最大等待时间（MAX(der.wait_time)）；同时嵌套查询sys.dm_exec_requests（别名der_block），通过blocking_session_id关联，直接获取阻塞者正在执行的 SQL（blocking_query_text）。
    • 核心特点：
      • 目标明确：只关注 “锁阻塞场景”，不包含无关的正常请求，结果更聚焦。
      • 价值信息更直接：
        • 统计维度：block_count（一个阻塞者阻塞了多少个会话，看影响范围）、max_wait_time（被阻塞会话的最长等待时间，看严重程度）；
        • 根源定位：同时输出 “被阻塞 SQL”（blocked_query_text）和 “阻塞者 SQL”（blocking_query_text），无需额外查询即可判断 “谁阻塞了谁，阻塞语句是什么”（比如阻塞者是长事务的UPDATE，被阻塞者是SELECT）。
    • 局限性：
      不包含正常运行的请求，无法全局监控请求状态；且因GROUP BY聚合，丢失了单个被阻塞请求的明细（如具体session_id、客户端地址）。

1.2.1 统计阻塞汇总信息：按照阻塞者汇总

SELECT TOP 10
    blocking_session_id AS blocking_session,  -- 阻塞者会话ID
    COUNT(*) AS block_count,  -- 阻塞次数
    MAX(der.wait_time) AS max_wait_time,  -- 被阻塞会话的最大等待时间（毫秒）
    DB_NAME(der.database_id) AS database_name,
    SUBSTRING(dest.text, (der.statement_start_offset/2)+1,
        ((CASE der.statement_end_offset
          WHEN -1 THEN DATALENGTH(dest.text)
          ELSE der.statement_end_offset
         END - der.statement_start_offset)/2)+1) AS blocked_query_text,  -- 被阻塞的SQL
    -- 阻塞者执行的SQL（需要关联阻塞者的sql_handle）
    (SELECT SUBSTRING(dest_block.text, (der_block.statement_start_offset/2)+1,
        ((CASE der_block.statement_end_offset
          WHEN -1 THEN DATALENGTH(dest_block.text)
          ELSE der_block.statement_end_offset
         END - der_block.statement_start_offset)/2)+1)
     FROM sys.dm_exec_requests der_block
     CROSS APPLY sys.dm_exec_sql_text(der_block.sql_handle) dest_block
     WHERE der_block.session_id = der.blocking_session_id) AS blocking_query_text
FROM 
    sys.dm_exec_requests der
CROSS APPLY 
    sys.dm_exec_sql_text(der.sql_handle) dest
WHERE 
    blocking_session_id <> 0  -- 只筛选被阻塞的会话
GROUP BY 
    blocking_session_id, der.database_id, dest.text, der.statement_start_offset, der.statement_end_offset
ORDER BY 
    block_count DESC;  -- 按阻塞次数排序

1.2.1 阻塞与被阻塞明细

SELECT 
    -- 阻塞者核心信息（解决列名错误，改用sys.dm_exec_connections的most_recent_sql_handle）
    der.blocking_session_id AS blocking_session_id,  -- 阻塞者会话ID
    bs.login_name AS blocking_login,                 -- 阻塞者登录名
    bc.client_net_address AS blocking_client_ip,     -- 阻塞者客户端IP
    bc.local_net_address AS blocking_local_ip,       -- 阻塞者本地IP（服务器端）
    -- 阻塞者的活跃请求信息（若存在）
    b.start_time AS blocking_request_start_time,     -- 阻塞者请求开始时间
    DATEDIFF(second, b.start_time, GETDATE()) AS blocking_request_duration_sec,  -- 阻塞者请求已运行秒数
    b.status AS blocking_request_status,             -- 阻塞者请求状态（如running/suspended）
    -- 阻塞者执行的SQL（优先取活跃请求的SQL，无则取最近执行的SQL，同时清洗换行符）
    REPLACE(REPLACE(
        SUBSTRING(bt.text, (ISNULL(b.statement_start_offset, 0)/2)+1,
            ((CASE WHEN ISNULL(b.statement_end_offset, -1) = -1 
                  THEN DATALENGTH(bt.text) 
                  ELSE b.statement_end_offset 
             END - ISNULL(b.statement_start_offset, 0))/2)+1
        ), CHAR(13), ' '), CHAR(10), ' ') AS blocking_query_text,

    -- 被阻塞者核心信息
    der.session_id AS blocked_session_id,            -- 被阻塞者会话ID
    des.login_name AS blocked_login,                 -- 被阻塞者登录名
    con.client_net_address AS blocked_client_ip,     -- 被阻塞者客户端IP
    der.start_time AS blocked_request_start_time,    -- 被阻塞者请求开始时间
    der.wait_time AS blocked_wait_time_ms,           -- 被阻塞者已等待毫秒数
    der.wait_type AS blocked_wait_type,              -- 等待类型（如LCK_M_X排它锁、LCK_M_S共享锁）
    der.wait_resource AS blocked_wait_resource,      -- 等待的具体资源（如：OBJECT: 123575513:1 [BULK_OPERATION]）
    der.command AS blocked_command_type,             -- 被阻塞者命令类型（如SELECT/UPDATE/DELETE）
    -- 被阻塞者执行的SQL（清洗换行符）
    REPLACE(REPLACE(
        SUBSTRING(dest.text, (der.statement_start_offset/2)+1,
            ((CASE WHEN der.statement_end_offset = -1 
                  THEN DATALENGTH(dest.text) 
                  ELSE der.statement_end_offset 
             END - der.statement_start_offset)/2)+1
        ), CHAR(13), ' '), CHAR(10), ' ') AS blocked_query_text,

    -- 公共信息
    DB_NAME(der.database_id) AS database_name        -- 涉及的数据库名
FROM 
    -- 主表：被阻塞的请求（仅筛选有阻塞者的请求）
    sys.dm_exec_requests der
-- 关联被阻塞者的会话信息（排除系统进程）
INNER JOIN 
    sys.dm_exec_sessions des 
    ON der.session_id = des.session_id 
    AND des.is_user_process = 1  -- 只看用户进程，排除SQL Server系统进程
-- 关联被阻塞者的连接信息（获取客户端IP）
LEFT JOIN 
    sys.dm_exec_connections con 
    ON der.session_id = con.session_id 
-- 关联阻塞者的会话信息（获取登录名等基础信息）
LEFT JOIN 
    sys.dm_exec_sessions bs 
    ON der.blocking_session_id = bs.session_id 
-- 关联阻塞者的连接信息（关键：获取most_recent_sql_handle，解决原报错）
LEFT JOIN 
    sys.dm_exec_connections bc 
    ON der.blocking_session_id = bc.session_id 
-- 关联阻塞者的活跃请求信息（若阻塞者当前有正在执行的请求）
LEFT JOIN 
    sys.dm_exec_requests b 
    ON der.blocking_session_id = b.session_id 
-- 获取阻塞者的SQL文本：优先用活跃请求的sql_handle，无则用连接的most_recent_sql_handle
OUTER APPLY 
    sys.dm_exec_sql_text(ISNULL(b.sql_handle, bc.most_recent_sql_handle)) bt
-- 获取被阻塞者的SQL文本（必关联，因der是活跃请求）
CROSS APPLY 
    sys.dm_exec_sql_text(der.sql_handle) dest
-- 筛选条件：仅保留有阻塞者的请求
WHERE 
    der.blocking_session_id <> 0
-- 排序：先按阻塞者ID分组（同一会话的阻塞关系集中展示），再按被阻塞者等待时间降序（优先看严重的）
ORDER BY 
    blocking_session_id ASC, 
    blocked_wait_time_ms DESC;

2. TOP CPU：“为什么 CPU 突然飙升？”（当前高 CPU）

核心目标：找出「当前正在执行」且 CPU 占用最高的 SQL 请求，解决 “CPU 飙升时，谁在吃资源” 的问题。

 

关键维度技术细节
数据来源（DMV） - sys.dm_exec_requests：当前活跃请求（核心，含 CPU 消耗、状态等）
- sys.dm_exec_sql_text：通过 sql_handle 关联请求对应的 SQL 语句
数据时效性 实时活跃数据：仅包含「当前正在运行」的请求（排除系统会话 session_id>50，SQL Server 系统会话 ID 通常 < 50），请求结束后数据消失。
核心关注指标 - cpu_time：请求已消耗的 CPU 时间（按此降序取 TOP10，定位 CPU 大户）
- status：请求状态（如 running 运行中、suspended 挂起）
- command：请求类型（如 SELECT/UPDATE/BACKUP）
过滤逻辑 排除系统会话（session_id>50），聚焦用户业务 SQL 的 CPU 消耗。 
SELECT TOP 10 [cpu_time],
[session_id],
[request_id],
[start_time] AS '开始时间',
[status] AS '状态',
[command] AS '命令',
dest.[text] AS 'sql语句',
DB_NAME([database_id]) AS '数据库名',
[blocking_session_id] AS '正在阻塞其他会话的会话ID',
[wait_type] AS '等待资源类型',
[wait_time] AS '等待时间',
[wait_resource] AS '等待的资源',
[reads] AS '物理读次数',
[writes] AS '写次数',
[logical_reads] AS '逻辑读次数',
[row_count] AS '返回结果行数'
FROM sys.[dm_exec_requests] AS der
CROSS APPLY
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest
WHERE [session_id]>50 --AND DB_NAME(der.[database_id])='UserSystemBusiness'
ORDER BY [cpu_time] DESC

3. TOP MEM：持续累积（直到缓存清理 / 重启）

核心目标：找出「历史执行过」且内存消耗（逻辑读）最高的 SQL 查询，解决 “长期内存占用高、或重复执行的低效 SQL” 问题（如某查询每次执行都读百万行数据，累积内存消耗极高）。

 

关键维度技术细节
数据来源（DMV） - sys.dm_exec_query_stats：历史查询统计信息（累积所有已执行查询的性能指标，包括已结束的查询）
- sys.dm_exec_sql_text：关联 SQL 语句
- sys.dm_exec_plan_attributes：通过执行计划属性获取数据库 ID（dbid）
数据时效性 历史累积数据：包含「已执行完成」和「当前正在执行」的查询，数据会持续累积（直到 SQL Server 重启或计划缓存清理），不随会话结束消失。
核心关注指标 - total_logical_reads：查询累积的逻辑读次数（逻辑读 = 从内存缓存读取数据，直接反映内存消耗，按此降序取 TOP10）
- execution_count：查询执行次数（判断是否为高频执行的低效 SQL）
- last_execution_time：最后一次执行时间（判断是否近期活跃）
- total_elapsed_time：累积总耗时（判断长期总消耗）
过滤逻辑 通过 pa.attribute = 'dbid' 关联数据库 ID，确保获取正确的数据库名称。 
SELECT TOP 10 
    DB_NAME(CAST(pa.value AS INT)) AS database_name,  -- 数据库名称
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS query_text,
    -- 内存和IO相关指标
    qs.total_physical_reads AS total_physical_reads,
    qs.total_logical_reads AS total_logical_reads,
    qs.total_logical_writes AS total_logical_writes,
    -- 时间相关信息（适配SQL Server 2012）
    qs.last_execution_time AS last_execution_time,  -- 最后一次执行结束时间
    DATEADD(millisecond, -qs.last_elapsed_time/1000, qs.last_execution_time) AS last_start_time,
    qs.last_elapsed_time AS last_execution_duration_microseconds,  -- 最后一次执行耗时（微秒）
    qs.total_elapsed_time AS total_elapsed_time,  -- 总耗时（微秒）
    -- 其他执行信息
    qs.execution_count AS execution_count,
    qs.total_worker_time AS total_worker_time
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY 
    sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE 
    pa.attribute = 'dbid' 
ORDER BY 
    qs.total_logical_reads DESC

4. TOP 磁盘 I/O（物理读写频繁的查询）

说明：监控物理读 / 写次数最多的查询，物理 I/O 操作直接与磁盘交互，是性能瓶颈的常见来源。
SELECT TOP 10
    DB_NAME(CAST(pa.value AS INT)) AS database_name,  -- 数据库名称
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS query_text,  -- 执行的SQL语句
    qs.total_physical_reads AS total_physical_reads,  -- 总物理读次数
    qs.total_physical_writes AS total_physical_writes,  -- 总物理写次数
    (qs.total_physical_reads + qs.total_physical_writes) AS total_physical_io,  -- 总物理I/O次数
    qs.execution_count AS execution_count,  -- 执行次数
    (qs.total_physical_reads + qs.total_physical_writes) / qs.execution_count AS avg_physical_io_per_execution  -- 每次执行平均物理I/O
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY 
    sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE 
    pa.attribute = 'dbid'  -- 筛选数据库ID属性
    AND qs.execution_count > 0  -- 排除未执行的查询
ORDER BY 
    total_physical_io DESC;  -- 按总物理I/O排序

5. TOP 执行次数（高频执行的查询）

说明：执行次数极高的查询，即使单次消耗低，累计影响也可能很大（如循环中执行的 SQL）。
SELECT TOP 10
    DB_NAME(CAST(pa.value AS INT)) AS database_name,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.execution_count AS execution_count,  -- 总执行次数
    qs.total_worker_time AS total_cpu_time,  -- 总CPU时间
    qs.total_elapsed_time AS total_elapsed_time,  -- 总耗时（微秒）
    qs.total_logical_reads AS total_logical_reads  -- 总逻辑读
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY 
    sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE 
    pa.attribute = 'dbid'
ORDER BY 
    execution_count DESC;  -- 按执行次数排序

6. TOP 平均耗时（单次执行耗时高的查询）

6.1 正在执行且运行时间超过指定阈值的查询（明细查询）

说明：监控当前正在执行且运行时间超过指定阈值（如下例 30 秒）的查询，及时发现卡顿任务。
依赖 sys.dm_exec_requests 视图，该视图记录的是当前正在执行的请求（实时活跃的查询），仅包含 “此刻正在运行” 的任务。
时间范围：仅限 “当前时间点正在执行” 的查询（实时监控）。
  • 核心维度：按 “当前已运行时长” 排序（run_seconds）。
  • 分析目标：实时捕获 “此刻正在卡住” 的查询（例如：已经运行了 5 分钟还没结束），用于紧急排查当前系统卡顿的原因（如用户反馈 “操作没反应” 时快速定位）。
SELECT 
    session_id,
    DB_NAME(database_id) AS database_name,
    command,  -- 命令类型（如SELECT、UPDATE）
    status,  -- 状态（如running、suspended）
    start_time,  -- 开始时间
    DATEDIFF(second, start_time, GETDATE()) AS run_seconds,  -- 已运行秒数
    SUBSTRING(dest.text, (der.statement_start_offset/2)+1,
        ((CASE der.statement_end_offset
          WHEN -1 THEN DATALENGTH(dest.text)
          ELSE der.statement_end_offset
         END - der.statement_start_offset)/2)+1) AS query_text
FROM 
    sys.dm_exec_requests der
CROSS APPLY 
    sys.dm_exec_sql_text(der.sql_handle) dest
WHERE 
    DATEDIFF(second, start_time, GETDATE()) > 30  -- 运行超过30秒
    AND session_id > 50  -- 排除系统会话
ORDER BY 
    run_seconds DESC;

6.2 平均执行时间长的查询（汇总分析）

说明：平均执行时间长的查询，可能导致用户操作卡顿（如复杂报表、大表全表扫描）。
依赖 sys.dm_exec_query_stats 视图，该视图记录的是历史累计的查询执行统计（自 SQL Server 启动或计划缓存清除后），反映的是 “过去所有执行过的查询” 的汇总数据。
时间范围：覆盖查询的全部历史执行记录（非实时）。
  • 核心维度：按 “平均单次执行耗时” 排序（avg_elapsed_seconds）。
  • 分析目标：找出 “单次执行就很慢” 的查询（例如：一次执行需要 10 秒，即使只执行过 1 次）。这类查询通常是复杂报表、大表全表扫描等，会直接导致用户操作卡顿。
SELECT TOP 10
    DB_NAME(CAST(pa.value AS INT)) AS database_name,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1) AS query_text,
    qs.execution_count AS execution_count,
    qs.total_elapsed_time/ 1000000 AS total_elapsed_time,  -- 总耗时（秒）
    (qs.total_elapsed_time / qs.execution_count) / 1000000 AS avg_elapsed_seconds,  -- 平均耗时（秒）
    qs.last_elapsed_time / 1000000 AS last_elapsed_seconds  -- 最后一次执行耗时（秒）
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY 
    sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE 
    pa.attribute = 'dbid'
    AND qs.execution_count > 0
ORDER BY 
    avg_elapsed_seconds DESC;  -- 按平均耗时排序

6.3 历史慢查：输出的SQL中去掉换行等（汇总分析）

同样依赖 sys.dm_exec_query_stats 视图，与 6.1 共享同一数据源，反映的是历史累计的查询执行统计。
时间范围：覆盖查询的全部历史执行记录（非实时）。

  • 核心维度：按 “总累计耗时” 排序（total_elapsed_seconds）。
  • 分析目标：找出 “累计消耗系统时间最多” 的查询。这类查询可能单次耗时不高（如每次 0.1 秒），但执行次数极多（如 100 万次），累计耗时达到 10 万秒，对系统资源的长期占用更大。
SELECT TOP 10
    -- 数据库名称
    DB_NAME(CAST(pa.value AS INT)) AS database_name,
    -- 提取SQL片段并转换为单行（去除换行符和回车符）
    REPLACE(
        REPLACE(
            SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
                ((CASE qs.statement_end_offset
                  WHEN -1 THEN DATALENGTH(qt.text)
                  ELSE qs.statement_end_offset
                 END - qs.statement_start_offset)/2)+1
            ),
            CHAR(13), ''  -- 去除回车符
        ),
        CHAR(10), ''  -- 去除换行符
    ) AS query_text,  -- 单行显示的SQL语句
    -- 执行次数
    qs.execution_count AS execution_count,
    -- 总耗时（转换为秒）
    CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS total_elapsed_seconds,
    -- 平均耗时（秒）
    CAST((qs.total_elapsed_time / qs.execution_count) / 1000000.0 AS DECIMAL(18,2)) AS avg_elapsed_seconds,
    -- 最长一次执行耗时（秒）
    CAST(qs.max_elapsed_time / 1000000.0 AS DECIMAL(18,2)) AS max_elapsed_seconds,
    -- 最近一次执行时间
    qs.last_execution_time AS last_execution_time
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY 
    sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE 
    pa.attribute = 'dbid'  -- 关联数据库ID
    AND qs.execution_count > 0  -- 排除未执行的查询
ORDER BY 
    total_elapsed_seconds DESC;  -- 按总耗时排序（累计影响最大）

7. TOP 阻塞源头（频繁阻塞其他会话的会话）

说明：找出经常作为阻塞者的会话，这些会话可能持有锁时间过长，导致其他会话等待。
SELECT TOP 10
    blocking_session_id AS blocking_session,  -- 阻塞者会话ID
    COUNT(*) AS block_count,  -- 阻塞次数
    MAX(der.wait_time) AS max_wait_time,  -- 被阻塞会话的最大等待时间（毫秒）
    DB_NAME(der.database_id) AS database_name,
    SUBSTRING(dest.text, (der.statement_start_offset/2)+1,
        ((CASE der.statement_end_offset
          WHEN -1 THEN DATALENGTH(dest.text)
          ELSE der.statement_end_offset
         END - der.statement_start_offset)/2)+1) AS blocked_query_text,  -- 被阻塞的SQL
    -- 阻塞者执行的SQL（需要关联阻塞者的sql_handle）
    (SELECT SUBSTRING(dest_block.text, (der_block.statement_start_offset/2)+1,
        ((CASE der_block.statement_end_offset
          WHEN -1 THEN DATALENGTH(dest_block.text)
          ELSE der_block.statement_end_offset
         END - der_block.statement_start_offset)/2)+1)
     FROM sys.dm_exec_requests der_block
     CROSS APPLY sys.dm_exec_sql_text(der_block.sql_handle) dest_block
     WHERE der_block.session_id = der.blocking_session_id) AS blocking_query_text
FROM 
    sys.dm_exec_requests der
CROSS APPLY 
    sys.dm_exec_sql_text(der.sql_handle) dest
WHERE 
    blocking_session_id <> 0  -- 只筛选被阻塞的会话
GROUP BY 
    blocking_session_id, der.database_id, dest.text, der.statement_start_offset, der.statement_end_offset
ORDER BY 
    block_count DESC;  -- 按阻塞次数排序

8. 缺失索引（性能提升潜力大的缺失索引）

说明：SQL Server 会记录潜在的缺失索引，这些索引能显著减少逻辑读，提升查询性能。
SELECT TOP 10
    CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact / 100.0)) AS improvement_measure,  -- 性能提升预估
    DB_NAME(mid.database_id) AS database_name,
    OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,  -- 表名
    mid.equality_columns,  -- 等值条件列（如WHERE a = 1）
    mid.inequality_columns,  -- 不等值条件列（如WHERE a > 1）
    mid.included_columns,  -- 包含列（INCLUDE子句）
    migs.user_seeks,  -- 用户查询寻求次数
    migs.user_scans,  -- 用户查询扫描次数
    migs.avg_user_impact  -- 平均性能提升百分比（%）
FROM 
    sys.dm_db_missing_index_details mid
INNER JOIN 
    sys.dm_db_missing_index_groups mig ON mid.index_handle = mig.index_handle
INNER JOIN 
    sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE 
    mid.database_id = DB_ID()  -- 当前数据库
ORDER BY 
    improvement_measure DESC;  -- 按预估提升排序

这些指标覆盖了 SQL Server 性能监控的主要维度（I/O、执行频率、耗时、阻塞、索引优化、实时长任务），可根据实际场景组合使用，定位性能瓶颈。
 posted on 2025-09-08 10:10  xibuhaohao  阅读(11)  评论(0)    收藏  举报