简书链接:sqlserver性能优化之查询耗时最长的语句
文章字数：400,阅读全文大约需要1分钟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000/1000 AS 平均执行时间_秒,  
    qs.execution_count AS 执行次数,                                         
    qs.total_elapsed_time / 1000/1000 AS 总执行时间_秒,                   
    qs.total_logical_reads AS 总逻辑读取次数,                        
    qs.total_physical_reads AS 总物理读取次数,                      
    qs.total_worker_time / 1000/1000 AS 总CPU时间_秒,                     
    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 查询语句文本,            -- 查询的实际语句
    qt.dbid AS 数据库ID,                                                     -- 数据库ID
    qt.objectid AS 对象ID                                                    -- 对象ID
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY
    平均执行时间_秒 DESC;  -- 根据平均执行时间降序排列

image.png

但是这样还不够，需要知道是什么程序执行的，是哪台电脑

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count / 1000.0 / 1000.0 AS 平均执行时间_秒,  -- 平均执行时间（秒）
    qs.execution_count AS 执行次数,                                                  -- 查询被执行的次数
    qs.total_elapsed_time / 1000.0 / 1000 AS 总执行时间_秒,                        -- 总执行时间（秒）
    qs.total_logical_reads AS 总逻辑读取次数,                                         -- 总逻辑读取次数
    qs.total_physical_reads AS 总物理读取次数,                                        -- 总物理读取次数
    qs.total_worker_time / 1000.0 / 1000 AS 总CPU时间_秒,                          -- 总CPU时间（秒）
    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 查询语句文本,                    -- 查询的实际语句
    s.host_name AS 主机名,                                                           -- 执行查询的主机名
    s.program_name AS 程序名,                                                        -- 执行查询的程序名
    s.login_name AS 登录名                                                           -- 执行查询的登录名
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
JOIN
    sys.dm_exec_requests AS r ON qs.sql_handle = r.sql_handle
JOIN
    sys.dm_exec_sessions AS s ON r.session_id = s.session_id
ORDER BY
    平均执行时间_秒 DESC;  -- 根据平均执行时间降序排列

更多参考
排查运行缓慢的查询 - SQL Server | Microsoft Learn

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
    r.session_id,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.wait_resource,
    t.text AS sql_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id > 50 -- 过滤掉系统进程
  AND r.status = 'suspended';