--- 平均I/O时间长的语句 USE tempdb SELECT TOP 10 (total_logical_reads/execution_count) AS avg_logical_reads, (total_logical_writes/execution_count) AS avg_logical_writes, (total_physical_reads/execution_count) AS avg_phys_reads, execution_count, statement_start_offset as stmt_start_offset, SUBSTRING(st.text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) , st.text --FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY SYS.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC /* 返回按批执行的 SQL 查询的文本,并提供有关它们的统计信息。 摘自msdn , sys.dm_exec_sql_text */ SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset; --------------------------------------- /* --CPU 开销较大的 --摘自MSDN */ USE AdventureWorks; GO SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM (query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text" FROM (SELECT QS.*, 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 FROM sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats GROUP BY query_stats.query_hash ORDER BY 2 DESC; GO /* 另外一种方法计算平均CPU的值 --摘自MSDN sys.dm_exec_sql_text */ SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time], SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY total_worker_time/execution_count DESC; /* 开销较大的查询 ---黄钊吉 */ SELECT ss.SUM_execution_count , t.text , ss.SUM_total_elapsed_time , ss.sum_total_worker_time , ss.sum_total_logical_reads , ss.sum_total_logical_writes FROM ( SELECT S.plan_handle , SUM(s.execution_count) SUM_Execution_count , SUM(s.total_elapsed_time) SUM_total_elapsed_time , SUM(s.total_worker_time) SUM_total_worker_time , SUM(s.total_logical_reads) SUM_total_logical_reads , SUM(s.total_logical_writes) SUM_total_logical_writes FROM sys.dm_exec_query_stats s GROUP BY S.plan_handle ) AS ss CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t ORDER BY sum_total_logical_reads DESC
CPU 及 IO 平均开销较大脚本
最新推荐文章于 2022-11-21 22:47:25 发布