如何寻找热点库、热点表、执行最频繁的sql 以及消耗资源最多的sql—dba必备脚本5(维护脚本5)


摘要:
在寻找热点表,热点数据库时,我们可以通过执行最频繁的sql脚本所涉及的表界定为“热点表”。
存在”热点表”的数据库,界定为”热点库”。
下文将分享获取各种DB运行状态的数据


   --1:获取前30逻辑读取次数或逻辑写入次数或CPU 时间  

SELECT TOP 30 b.TEXT as [命令内容],
a.execution_count,
a.total_logical_reads, a.last_logical_reads,
a.total_logical_writes, a.last_logical_writes,
a.total_worker_time,
a.last_worker_time,
a.total_elapsed_time/1000000 as [执行时间秒],
a.last_elapsed_time/1000000 as [最后一次执行时间秒],
a.last_execution_time,
c.query_plan
FROM sys.dm_exec_query_stats a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) c
ORDER BY a.total_logical_reads DESC -- 逻辑读取次数
--ORDER BY a.total_logical_writes DESC -- 逻辑写入次数
--ORDER BY a.total_worker_time DESC -- CPU 时间

--2:获取前30执行的存储过程的总工作时间(CPU压力)
SELECT TOP 30 b.text AS '存储过程名称', a.total_worker_time AS '总工作时间',
a.total_worker_time/a.execution_count AS '平均工作时间',
a.execution_count AS '总执行次数',
ISNULL(a.total_elapsed_time/a.execution_count, 0) AS '平均执行时间',
a.max_logical_reads, a.max_logical_writes,
DATEDIFF(Minute, a.creation_time, getdate()) AS '缓存时间'
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
WHERE b.dbid = db_id() -- 当前数据库
ORDER BY a.total_worker_time DESC

--3: 获取前30 存储过程的 逻辑辑写入/分钟
SELECT TOP 20 b.text AS '存储过程名称', a.total_logical_writes, a.total_logical_writes/a.execution_count AS '平均逻辑写入时间',
a.total_logical_writes/DATEDIFF(Minute, a.creation_time, getdate()) AS '平均逻辑写入时间-分钟',
a.execution_count AS '执行总次数',
a.total_worker_time/a.execution_count AS '平均工作时间',
a.total_worker_time AS '总工作时间',
a.total_elapsed_time/a.execution_count AS '平均执行时间',
a.max_logical_reads, a.max_logical_writes, a.total_physical_reads,
a.total_physical_reads/a.execution_count AS '平均物理读时间'
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
WHERE b.dbid = db_id() -- 当前数据库
ORDER BY a.total_logical_writes DESC

--4: 获取前30 存储过程的 逻辑读取(内存压力)
SELECT TOP 30 b.text AS '存储过程名称',
a.total_logical_reads/a.execution_count AS '平均逻辑读次数',
a.total_logical_writes, a.total_logical_writes/a.execution_count AS '平均逻辑写入时间',
a.total_logical_writes/DATEDIFF(Minute, a.creation_time, getdate()) AS '平均逻辑写入时间-分钟',
a.execution_count AS '执行总次数',
a.total_worker_time/a.execution_count AS '平均工作时间',
a.total_worker_time AS '总工作时间',
a.total_elapsed_time/a.execution_count AS '平均执行时间',
a.max_logical_reads, a.max_logical_writes, a.total_physical_reads,
a.total_physical_reads/a.execution_count AS '平均物理读时间'
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
WHERE b.dbid = db_id() -- 当前数据库
ORDER BY a.total_logical_reads DESC

--5: 获取前30 存储过程的 物理读取 (读取 I/O 压力)
SELECT TOP 30 b.text AS '存储过程名称',
a.total_physical_reads, a.total_physical_reads/a.execution_count AS '平均物理读取次数',
a.total_logical_reads/a.execution_count AS '平均逻辑读次数',
a.total_logical_writes, a.total_logical_writes/a.execution_count AS '平均逻辑写入时间',
a.total_logical_writes/DATEDIFF(Minute, a.creation_time, getdate()) AS '平均逻辑写入时间-分钟',
a.execution_count AS '执行总次数',
a.total_worker_time/a.execution_count AS '平均工作时间',
a.total_worker_time AS '总工作时间',
a.total_elapsed_time/a.execution_count AS '平均执行时间',
a.max_logical_reads, a.max_logical_writes, a.total_physical_reads,
a.total_physical_reads/a.execution_count AS '平均物理读时间'
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
WHERE b.dbid = db_id() -- 当前数据库
ORDER BY a.total_physical_reads DESC

--6: 获取前30 存储过程的 执行次数
SELECT TOP 30 b.text AS '存储过程名称', a.execution_count AS '执行总次数',
a.total_physical_reads, a.total_physical_reads/a.execution_count AS '平均物理读取次数',
a.total_logical_reads/a.execution_count AS '平均逻辑读次数',
a.total_logical_writes, a.total_logical_writes/a.execution_count AS '平均逻辑写入时间',
a.total_logical_writes/DATEDIFF(Minute, a.creation_time, getdate()) AS '平均逻辑写入时间-分钟',
a.total_worker_time/a.execution_count AS '平均工作时间',
a.total_worker_time AS '总工作时间',
a.total_elapsed_time/a.execution_count AS '平均执行时间',
a.max_logical_reads, a.max_logical_writes, a.total_physical_reads,
a.total_physical_reads/a.execution_count AS '平均物理读时间'
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
WHERE b.dbid = db_id() -- 当前数据库
ORDER BY a.execution_count DESC