人人都是 DBA（XII）查询信息收集脚本汇编

原文: 人人都是 DBA（XII）查询信息收集脚本汇编

什么？有个 SQL 执行了 8 秒！

哪里出了问题？臣妾不知道啊，得找 DBA 啊。

DBA 人呢？离职了！！擦！！！

程序员在无处寻求帮助时，就得想办法自救，努力让自己变成 "伪 DBA"。

索引

1. 按页编号查看数据表信息
2. 获取查询 SELECT 语句的执行次数排名
3. 看看哪些 Ad-hoc Query 在浪费资源
4. 查看当前处于等待状态的 Task 在等什么
5. 查询谁在占着 Session 连接
6. 查询程序占用的 SPID 信息
7. 查询所有执行 SQL 对应的 sql_handle
8. 查询最近 60 秒平均执行时间超过 300 毫秒的 SQL 语句
9. 查询最近 60 秒平均执行时间超过 100 毫秒的非 SELECT 语句
10. 查询最近 60 秒累计总执行次数大于 1000 次的 SQL 语句
11. 查询前 10 个可能是性能最差的 SQL 语句
12. 看看当前哪些查询正在活跃着

按页编号查看数据表信息

SELECT sc.[name] AS [schema]
    ,o.[name] AS [table_name]
    ,o.type_desc
    ,obd.[file_id]
    ,obd.page_id
    ,obd.page_level
    ,obd.row_count
    ,obd.free_space_in_bytes
    ,obd.is_modified
    ,obd.numa_node
FROM sys.dm_os_buffer_descriptors AS obd
JOIN sys.allocation_units AS au ON obd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.objects AS o ON p.[object_id] = o.[object_id]
JOIN sys.schemas AS sc ON o.[schema_id] = sc.[schema_id]
WHERE database_id = DB_ID()
    AND o.is_ms_shipped = 0
ORDER BY obd.page_id
    ,o.[name]

获取查询 SELECT 语句的执行次数排名

SQL Server 2012 版本

SELECT TOP (100) qs.execution_count
    ,qs.total_rows
    ,qs.last_rows
    ,qs.min_rows
    ,qs.max_rows
    ,qs.last_elapsed_time
    ,qs.min_elapsed_time
    ,qs.max_elapsed_time
    ,total_worker_time
    ,total_logical_reads
    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (
            CASE 
                WHEN qs.statement_end_offset = - 1
                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                ELSE qs.statement_end_offset
                END - qs.statement_start_offset
            ) / 2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);

SQL Server 2008 R2 版本

SELECT TOP (100) qs.execution_count
    ,qs.last_elapsed_time
    ,qs.min_elapsed_time
    ,qs.max_elapsed_time
    ,total_worker_time
    ,total_logical_reads
    ,SUBSTRING(qt.[text], qs.statement_start_offset / 2 + 1, (
            CASE 
                WHEN qs.statement_end_offset = - 1
                    THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                ELSE qs.statement_end_offset
                END - qs.statement_start_offset
            ) / 2) AS query_text
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);

看看哪些 Ad-hoc Query 在浪费资源

SELECT TOP (50) [text] AS [QueryText]
    ,cp.cacheobjtype
    ,cp.objtype
    ,cp.size_in_bytes / 1024 AS [Plan Size in KB]
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cp.cacheobjtype = N'Compiled Plan'
    AND cp.objtype IN (
        N'Adhoc'
        ,N'Prepared'
        )
    AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC
OPTION (RECOMPILE);

查看当前处于等待状态的 Task 在等什么

SELECT dm_ws.wait_duration_ms
    ,dm_ws.wait_type
    ,dm_es.STATUS
    ,dm_t.TEXT
    ,dm_qp.query_plan
    ,dm_ws.session_ID
    ,dm_es.cpu_time
    ,dm_es.memory_usage
    ,dm_es.logical_reads
    ,dm_es.total_elapsed_time
    ,dm_es.program_name
    ,DB_NAME(dm_r.database_id) DatabaseName
    ,dm_ws.blocking_session_id
    ,dm_r.wait_resource
    ,dm_es.login_name
    ,dm_r.command
    ,dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text(dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan(dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1;

查询谁在占着 Session 连接

CREATE TABLE #sp_who2 (
    SPID INT
    ,STATUS VARCHAR(255)
    ,LOGIN VARCHAR(255)
    ,HostName VARCHAR(255)
    ,BlkBy VARCHAR(255)
    ,DBName VARCHAR(255)
    ,Command VARCHAR(255)
    ,CPUTime INT
    ,DiskIO INT
    ,LastBatch VARCHAR(255)
    ,ProgramName VARCHAR(255)
    ,SPID2 INT
    ,REQUESTID INT
    )

INSERT INTO #sp_who2
EXEC sp_who2

SELECT *
FROM #sp_who2 w
--WHERE w.ProgramName = 'xxx'

DROP TABLE #sp_who2

查询程序占用的 SPID 信息

SELECT spid
    ,a.[status]
    ,hostname
    ,program_name
    ,cmd
    ,cpu
    ,physical_io
    ,blocked
    ,b.[name]
    ,loginame
FROM master.dbo.sysprocesses a
INNER JOIN master.dbo.sysdatabases b ON a.dbid = b.dbid
where hostname != ''
ORDER BY program_name

查询所有执行 SQL 对应的 sql_handle

DECLARE @current_sql_handle BINARY (20);
DECLARE @sql_text_list TABLE (
    sql_handle BINARY (20)
    ,TEXT NVARCHAR(max)
    );

DECLARE sql_handle_cursor CURSOR
FOR
SELECT sp.sql_handle
FROM sys.sysprocesses sp
WHERE sp.sql_handle != 0x0000000000000000000000000000000000000000
    --AND sp.program_name = 'xxxx'
    ;

OPEN sql_handle_cursor

FETCH NEXT
FROM sql_handle_cursor
INTO @current_sql_handle

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @sql_text_list (
        sql_handle
        ,TEXT
        )
    SELECT @current_sql_handle
        ,est.TEXT
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(@current_sql_handle) est;

    FETCH NEXT
    FROM sql_handle_cursor
    INTO @current_sql_handle
END

SELECT DISTINCT *
FROM @sql_text_list tl
WHERE tl.TEXT NOT LIKE '%statement_start_offset%';

CLOSE sql_handle_cursor

DEALLOCATE sql_handle_cursor

查询最近 60 秒平均执行时间超过 300 毫秒的 SQL 语句

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (
            (
                (
                    CASE statement_end_offset
                        WHEN - 1
                            THEN DATALENGTH(st.TEXT)
                        ELSE qs.statement_end_offset
                        END
                    ) - qs.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,last_execution_time
    ,total_elapsed_time / execution_count avg_elapsed_time
    ,total_physical_reads
    ,total_logical_reads
    ,total_logical_writes
    ,execution_count
    ,total_worker_time
    ,total_elapsed_time
    ,creation_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE())
    AND st.TEXT NOT LIKE '%statement_start_offset%'
    AND total_elapsed_time / execution_count >= 300
ORDER BY last_execution_time DESC;

查询最近 60 秒平均执行时间超过 100 毫秒的非 SELECT 语句

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (
            (
                (
                    CASE statement_end_offset
                        WHEN - 1
                            THEN DATALENGTH(st.TEXT)
                        ELSE qs.statement_end_offset
                        END
                    ) - qs.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,last_execution_time
    ,total_elapsed_time / execution_count avg_elapsed_time
    ,total_physical_reads
    ,total_logical_reads
    ,total_logical_writes
    ,execution_count
    ,total_worker_time
    ,total_elapsed_time
    ,creation_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE last_execution_time > DATEADD(SECOND, - 60, GETDATE())
    AND st.TEXT NOT LIKE '%statement_start_offset%'
    AND execution_count < 100
    AND total_elapsed_time / execution_count > 100
    AND SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (
            (
                (
                    CASE statement_end_offset
                        WHEN - 1
                            THEN DATALENGTH(st.TEXT)
                        ELSE qs.statement_end_offset
                        END
                    ) - qs.statement_start_offset
                ) / 2
            ) + 1) NOT LIKE 'SELECT%'
ORDER BY last_execution_time DESC;

查询最近 60 秒累计总执行次数大于 1000 次的 SQL 语句

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (
            (
                (
                    CASE statement_end_offset
                        WHEN - 1
                            THEN DATALENGTH(st.TEXT)
                        ELSE qs.statement_end_offset
                        END
                    ) - qs.statement_start_offset
                ) / 2
            ) + 1) AS statement_text
    ,total_elapsed_time / execution_count / 1000 AS avg_elapsed_time_by_ms
    ,last_execution_time
    ,total_elapsed_time
    ,execution_count
    ,total_worker_time
    ,total_physical_reads
    ,total_logical_reads
    ,total_logical_writes
    ,creation_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.execution_count > 1000
    AND last_execution_time > DATEADD(SECOND, - 60, GETDATE())
    --AND (
    --    st.TEXT LIKE '%[[]AAA]%'
    --    OR st.TEXT LIKE '%[[]BBB]%'
    --    OR st.TEXT LIKE '%[[]CCC]%'
    --    )
ORDER BY total_elapsed_time / execution_count DESC;

查询前 10 个可能是性能最差的 SQL 语句

SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count"
    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

看看当前哪些查询正在活跃着

Adam Machanic 发布了一个查询活跃 SQL 的查询脚本，篇幅极长，请到发布地址下载。

• Who is Active v11.11

 

《人人都是 DBA》系列文章索引：

序号	名称
1	人人都是 DBA（I）SQL Server 体系结构
2	人人都是 DBA（II）SQL Server 元数据
3	人人都是 DBA（III）SQL Server 调度器
4	人人都是 DBA（IV）SQL Server 内存管理
5	人人都是 DBA（V）SQL Server 数据库文件
6	人人都是 DBA（VI）SQL Server 事务日志
7	人人都是 DBA（VII）B 树和 B+ 树
8	人人都是 DBA（VIII）SQL Server 页存储结构
9	人人都是 DBA（IX）服务器信息收集脚本汇编
10	人人都是 DBA（X）资源信息收集脚本汇编
11	人人都是 DBA（XI）I/O 信息收集脚本汇编
12	人人都是 DBA（XII）查询信息收集脚本汇编
13	人人都是 DBA（XIII）索引信息收集脚本汇编
14	人人都是 DBA（XIV）存储过程信息收集脚本汇编
15	人人都是 DBA（XV）锁信息收集脚本汇编

本系列文章《人人都是 DBA》由 Dennis Gao 发表自博客园，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载行为均为耍流氓。