2 3   4    Y 5    c:adFile 6     人人都是 DBA（XII）查询信息收集脚本汇编 - 11GX
首页 > 人人都是 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 = - 1THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2ELSE qs.statement_end_offsetEND - 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 = - 1THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2ELSE qs.statement_end_offsetEND - 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_who2SELECT *
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_cursorFETCH NEXT
FROM sql_handle_cursor
INTO @current_sql_handleWHILE @@FETCH_STATUS = 0
BEGININSERT INTO @sql_text_list (sql_handle,TEXT)SELECT @current_sql_handle,est.TEXTFROM sys.dm_exec_query_stats qsCROSS APPLY sys.dm_exec_sql_text(@current_sql_handle) est;FETCH NEXTFROM sql_handle_cursorINTO @current_sql_handle
ENDSELECT DISTINCT *
FROM @sql_text_list tl
WHERE tl.TEXT NOT LIKE '%statement_start_offset%';CLOSE sql_handle_cursorDEALLOCATE sql_handle_cursor

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

SELECT SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (((CASE statement_end_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - 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_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - 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 < 100AND total_elapsed_time / execution_count > 100AND SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, (((CASE statement_end_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - 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_offsetWHEN - 1THEN DATALENGTH(st.TEXT)ELSE qs.statement_end_offsetEND) - 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 > 1000AND 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 发表自博客园，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载行为均为耍流氓。

更多相关：

  • jmeter吞吐量图形显示_Jmeter系列（43）- 详解 Jmeter 图形化 HTML 压测报告之 Charts 模块...

    如果你想从头学习Jmeter，可以看看这个系列的文章哦Charts 介绍包含了各种详细信息图表，比 GUI 模式的图表好看且易懂多了！做性能测试，如何发现是否有性能瓶颈？必须从结果图表中找到鸭！而 html 报告将性能测试可能需要用到的图表都加进去了，可谓是6666一共有三大模块Over TimeThroughputResponse...

  • Udacity机器人软件工程师课程笔记（二十三) - 控制（其一）- PID控制及其python实现

    控制（Controls） 1.PID控制简介 在工程实际中，应用最为广泛的调节器控制规律为比例、积分、微分控制，简称PID控制，又称PID调节。PID控制器问世至今已有近70年历史，它 以其结构简单、稳定性好、工作可靠、调整方便而成为工业控制的主要技术之一。当被控对象的结构和参数不能完全掌握，或得不到精确的数学模型时，控制理论的...

  • printf格式化输出几点注记

    搞了很多年c/c++，有很多细小的东西，曾经不止一次遇到，可是一直都是放在零散的地方,要用的时候怎么也找不到，今天，我痛下决心，改掉不良习惯，把这些经验或是tips记录在这里，便于日后查找。 1.在统计网络下载信息时，如何表达文件大小？ 下面是输出结果 2.打印size_t类型数据的长度，使用%lu。 下面是一个使...

  • PHP的一种缓存方案静态化

    1,解决的问题。 2.如何实现。 面对大流量网站频繁访问数据库的一种优化，比如博客网站。不可能每个人查看都访问一次数据库。为了解决大量不必要访问的问题。 可以把第一次的内容保存为html页面。再以后定义的过期时间内都访问该静态页面。 以下是一个小的demo index.php来实现静态化的主要工作。 1

  • 亮剑.NET的系列文章之.NET实现三层架构（三）

        最近一直在学习三层架构，前些天同样也写了一篇同样的博客，今天主要是通过一个登录的实例给大家讲解每部分的作用和相应代码的实现。     先将实现三层架构的UML图给大家，帮助大家更好的理解三层。               １. UI作用 (1) 向用户展示特定业务数据 (2) 采集用户的输入信息和操作 Publ...

  • 用Asp.net实现简单的文字水印

     经常看见MOP上有人贴那种动态的图片，就是把一个字符串作为参数传给一个动态网页，就会生成一个带有这个字符串的图片，这个叫做文字水印。像什么原来的熊猫系列，还有后来的大树和金条，都挺有意思。这东西看着挺好玩的，想做个玩玩。查了查资料，发现用.net来做这个是很容易的，就用Asp.net写了一个非常简单的。全部的代码如下： <%@...

  • hibernate join fetch

    那天去了达内，试听了2个小时的课，刚好讲的他们的当当网项目，讲hibernate表关系映射这段来了，不过也够呛，投影仪看的眼睛点都不舒服，当时讲的也没认真听，听了个大概，就记住那个老师一句话了，说是有经验的人用的，一般人不告诉，就是说这个join fetch 用法，回来查了一下，用起来比较爽，今晚突然来了兴致，小记一下。 join f...

  • 取消水晶报表的数据库登录框 分享

              这两天在和斌做后台中的报表，暂定使用水晶报表，目前还只是处于对水晶报表的初级应用阶段，也就是知道如何 汇个总、写个函数、传个参数。           问题总是层出不穷，在最后整合报表，进行报表显示测试的时候，发现每次更新数据显示（除第一次）时都会蹦出一个“登录数据库的信息提示界面”很是苦闷，并且这肯�...

  • XML Show

     1 2 3   4    Y 5    c:adFile 6    

©2024 11GX.COM