什么?有个 SQL 执行了 8 秒!
哪里出了问题?臣妾不知道啊,得找 DBA 啊。
DBA 人呢?离职了!!擦!!!
程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA"。
索引
- 按页编号查看数据表信息
- 获取查询 SELECT 语句的执行次数排名
- 看看哪些 Ad-hoc Query 在浪费资源
- 查看当前处于等待状态的 Task 在等什么
- 查询谁在占着 Session 连接
- 查询程序占用的 SPID 信息
- 查询所有执行 SQL 对应的 sql_handle
- 查询最近 60 秒平均执行时间超过 300 毫秒的 SQL 语句
- 查询最近 60 秒平均执行时间超过 100 毫秒的非 SELECT 语句
- 查询最近 60 秒累计总执行次数大于 1000 次的 SQL 语句
- 查询前 10 个可能是性能最差的 SQL 语句
- 看看当前哪些查询正在活跃着
按页编号查看数据表信息
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 的查询脚本,篇幅极长,请到发布地址下载。
《人人都是 DBA》系列文章索引:
序号 |
名称 |
1 |
|
2 |
|
3 |
|
4 |
|
5 |
|
6 |
|
7 |
|
8 |
|
9 |
|
10 |
|
11 |
|
12 |
|
13 |
|
14 |
|
15 |
本系列文章《人人都是 DBA》由 Dennis Gao 发表自博客园,未经作者本人同意禁止任何形式的转载,任何自动或人为的爬虫转载行为均为耍流氓。
人人都是 DBA(XII)查询信息收集脚本汇编的更多相关文章
-
人人都是 DBA(XV)锁信息收集脚本汇编
什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...
-
人人都是 DBA(XIV)存储过程信息收集脚本汇编
什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...
-
人人都是 DBA(XIII)索引信息收集脚本汇编
什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...
-
人人都是 DBA(XI)I/O 信息收集脚本汇编
什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...
-
人人都是 DBA(X)资源信息收集脚本汇编
什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...
-
人人都是 DBA(IX)服务器信息收集脚本汇编
什么?有个 SQL 执行了 8 秒! 哪里出了问题?臣妾不知道啊,得找 DBA 啊. DBA 人呢?离职了!!擦!!! 程序员在无处寻求帮助时,就得想办法自救,努力让自己变成 "伪 DBA& ...
-
人人都是 DBA(VIII)SQL Server 页存储结构
当在 SQL Server 数据库中创建一张表时,会在多张系统基础表中插入所创建表的信息,用于管理该表.通过目录视图 sys.tables, sys.columns, sys.indexes 可以查看 ...
-
人人都是 DBA(VI)SQL Server 事务日志
SQL Server 的数据库引擎通过事务服务(Transaction Services)提供事务的 ACID 属性支持.ACID 属性包括: 原子性(Atomicity) 一致性(Consisten ...
-
人人都是 DBA(V)SQL Server 数据库文件
SQL Server 数据库安装后会包含 4 个默认系统数据库:master, model, msdb, tempdb. SELECT [name] ,database_id ,suser_sname ...
随机推荐
-
bzoj1616
水水啊,直接搜就行,不过bfs好像会mle(一定是我太菜了QAQ) #include<iostream> #include<algorithm> #include<cst ...
-
weblogic解密工具
import org.bouncycastle.jce.provider.BouncyCastleProvider; import sun.misc.BASE64Decoder; import jav ...
-
【Android】屏幕尺寸的表示
1. 单位 - px(pixel): 普通的像素点描述,位图中的一个颜色点(RGBA或者YUV) - dip(device independent pixels): 设备独立像素. - dp: 与di ...
-
Util6 MIS2.0 (优六信息化管理系统框架)
Util6 MIS一套基于ASP.NET MVC5 + Layui 开发的通用信息化管理系统快速开发框架 系统操作界面简洁, 项目结构清晰,功能模块化设计,支撑框架轻量高效,代码层级分离,注释完整,可 ...
-
C# Directory.Exists() 文件存在但返回一直为false
备注:这是一个低级错误,起始真正的原因不是访问权限的问题. 真正的原因是:这个程序要读取远程电脑上共享文件夹里的文件,但是没有远程访问代码,导致找不到相关的目录.所以才报错! 查询一个文件,但程序突然 ...
-
安装Windows10系统注意事项
硬盘的AHCI开启: 报错解决:将Secure Boot 设置为Disabled win10系统下载地址:ed2k://|file|cn_windows_10_multi-edition_versi ...
-
三、存储过程(Stored Procedure)与游标(Cursor)
一.存储过程 一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数,来执行它. 在大型数据库中,存储过程和触发器具有重要的作用.无论是存储过程还是触发器,都 ...
-
Android 7.0 行为变更
Android 7.0 除了提供诸多新特性和功能外,还对系统和 API 行为做出了各种变更.本文重点介绍您应该了解并在开发应用时加以考虑的一些主要变更. 如果您之前发布过 Android 应用,请注意 ...
-
【嵌入式】——makefiles
汇编通用makefile: 命令行编辑: 编译 arm-linux-as -march=armv5te -o led.o led.s -march 指定的指令集的版本 指定架构 连接 arm-linu ...
-
netty7---自定义序列化接口
package com.cn.core; import java.nio.ByteOrder; import java.nio.charset.Charset; import java.util.Ar ...