人人都是 DBA(XII)查询信息收集脚本汇编

时间:2022-08-24 20:16:57

什么?有个 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]

人人都是 DBA(XII)查询信息收集脚本汇编

获取查询 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);

人人都是 DBA(XII)查询信息收集脚本汇编

看看哪些 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);

人人都是 DBA(XII)查询信息收集脚本汇编

查看当前处于等待状态的 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;

人人都是 DBA(XII)查询信息收集脚本汇编

查询谁在占着 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

人人都是 DBA(XII)查询信息收集脚本汇编

查询程序占用的 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

人人都是 DBA(XII)查询信息收集脚本汇编

查询所有执行 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

人人都是 DBA(XII)查询信息收集脚本汇编

查询最近 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;

人人都是 DBA(XII)查询信息收集脚本汇编

查询最近 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;

人人都是 DBA(XII)查询信息收集脚本汇编

查询最近 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;

人人都是 DBA(XII)查询信息收集脚本汇编

查询前 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

人人都是 DBA(XII)查询信息收集脚本汇编

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

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

人人都是 DBA(XII)查询信息收集脚本汇编

《人人都是 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 发表自博客园,未经作者本人同意禁止任何形式的转载,任何自动或人为的爬虫转载行为均为耍流氓。

人人都是 DBA(XII)查询信息收集脚本汇编的更多相关文章

  1. 人人都是 DBA(XV)锁信息收集脚本汇编

    什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...

  2. 人人都是 DBA(XIV)存储过程信息收集脚本汇编

    什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...

  3. 人人都是 DBA(XIII)索引信息收集脚本汇编

    什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...

  4. 人人都是 DBA(XI)I&sol;O 信息收集脚本汇编

    什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...

  5. 人人都是 DBA(X)资源信息收集脚本汇编

    什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...

  6. 人人都是 DBA(IX)服务器信息收集脚本汇编

    什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...

  7. 人人都是 DBA(VIII)SQL Server 页存储结构

    当在 SQL Server 数据库中创建一张表时,会在多张系统基础表中插入所创建表的信息,用于管理该表.通过目录视图 sys.tables, sys.columns, sys.indexes 可以查看 ...

  8. 人人都是 DBA(VI)SQL Server 事务日志

    SQL Server 的数据库引擎通过事务服务(Transaction Services)提供事务的 ACID 属性支持.ACID 属性包括: 原子性(Atomicity) 一致性(Consisten ...

  9. 人人都是 DBA(V)SQL Server 数据库文件

    SQL Server 数据库安装后会包含 4 个默认系统数据库:master, model, msdb, tempdb. SELECT [name] ,database_id ,suser_sname ...

随机推荐

  1. bzoj1616

    水水啊,直接搜就行,不过bfs好像会mle(一定是我太菜了QAQ) #include<iostream> #include<algorithm> #include<cst ...

  2. weblogic解密工具

    import org.bouncycastle.jce.provider.BouncyCastleProvider; import sun.misc.BASE64Decoder; import jav ...

  3. 【Android】屏幕尺寸的表示

    1. 单位 - px(pixel): 普通的像素点描述,位图中的一个颜色点(RGBA或者YUV) - dip(device independent pixels): 设备独立像素. - dp: 与di ...

  4. Util6 MIS2&period;0 &lpar;优六信息化管理系统框架&rpar;

    Util6 MIS一套基于ASP.NET MVC5 + Layui 开发的通用信息化管理系统快速开发框架 系统操作界面简洁, 项目结构清晰,功能模块化设计,支撑框架轻量高效,代码层级分离,注释完整,可 ...

  5. C&num; Directory&period;Exists&lpar;&rpar; 文件存在但返回一直为false

    备注:这是一个低级错误,起始真正的原因不是访问权限的问题. 真正的原因是:这个程序要读取远程电脑上共享文件夹里的文件,但是没有远程访问代码,导致找不到相关的目录.所以才报错! 查询一个文件,但程序突然 ...

  6. 安装Windows10系统注意事项

    硬盘的AHCI开启: 报错解决:将Secure Boot 设置为Disabled  win10系统下载地址:ed2k://|file|cn_windows_10_multi-edition_versi ...

  7. 三、存储过程(Stored Procedure)与游标(Cursor)

    一.存储过程 一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数,来执行它. 在大型数据库中,存储过程和触发器具有重要的作用.无论是存储过程还是触发器,都 ...

  8. Android 7&period;0 行为变更

    Android 7.0 除了提供诸多新特性和功能外,还对系统和 API 行为做出了各种变更.本文重点介绍您应该了解并在开发应用时加以考虑的一些主要变更. 如果您之前发布过 Android 应用,请注意 ...

  9. 【嵌入式】——makefiles

    汇编通用makefile: 命令行编辑: 编译 arm-linux-as -march=armv5te -o led.o led.s -march 指定的指令集的版本 指定架构 连接 arm-linu ...

  10. netty7---自定义序列化接口

    package com.cn.core; import java.nio.ByteOrder; import java.nio.charset.Charset; import java.util.Ar ...