VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 编程开发 > 汇编语言 >
  • 人人都是 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)锁信息收集脚本汇编

出处:https://www.cnblogs.com/gaochundong/p/everyone_is_a_dba_query_info_collection.html


相关教程