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

转自:http://www.maomao365.com/?p=4764

摘要:
在寻找热点表，热点数据库时，我们可以通过执行最频繁的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  

转载于:https://blog.51cto.com/13618148/2073990