查看系统中cpu 开销最大的一些sql查询

最新推荐文章于 2022-06-11 20:52:59 发布

最新推荐文章于 2022-06-11 20:52:59 发布 · 268 阅读

文章标签：

#runtime

此SQL脚本用于找出在SQL Server上占用CPU最多的前300条查询。它通过筛选最近两小时内执行次数超过设定阈值且平均CPU时间超过指定毫秒数的查询来实现。该脚本适用于SQL 2008及以后的版本。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/*------------------------------------------------------------------------------+ #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : | #|{>/------------------------------------------------------------------------\<}| #|: | Author : 小爱(Beirut) | #|: | Description: 查找占用upu 最多的一些脚本 | #|: | SQL Version: 适用于 SQL 2012, SQL 2008 R2, SQL 2008 | #|: | Copyright : 免费使用和共享e /^(o.o)^\ | #|: | Create Date: 2012-04-13 16:50:20.577 | #|: | Revision : Version: 1.1 持续更新ing | #|{>\------------------------------------------------------------------------/<}| #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : | #+-----------------------------------------------------------------------------*/ select GETDATE() use tempdb go IF object_id('tempdb..#FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[#FindTopCPUQueries_set1] GO declare @ServerTime datetime = getdate() , @ConvertMiliSeconds bigint = 1000 , @FilterMoreThanMiliSeconds bigint = 1 , @FilterHours bigint = 2 , @execution_count bigint = 2 , @debugFlg bit = 0 if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds , @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours , @execution_count as execution_count select top 300 @@servername as servername,@ServerTime as runtime ,isnull(db_name(QueryText.dbid),'PreparedSQL') as DBName ,SUBSTRING(QueryText.text,(QueryStats.statement_start_offset/2)+1, (isnull(( CASE QueryStats.statement_end_offset WHEN -1 THEN DATALENGTH(QueryText.text) WHEN 0 THEN DATALENGTH(QueryText.text) ELSE QueryStats.statement_end_offset END - QueryStats.statement_start_offset ) ,0)/2)+ 1 ) AS QueryExecuted ,total_worker_time AS total_worker_time ,QueryStats.execution_count as execution_count ,statement_start_offset,statement_end_offset ,( case when QueryText.dbid is null then OBJECT_NAME(QueryText.objectid) else OBJECT_NAME(QueryText.objectid, QueryText.dbid) end ) as ObjectName ,query_hash ,plan_handle ,sql_handle into #FindTopCPUQueries_set1 from sys.dm_exec_query_stats as QueryStats cross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryText where QueryStats.query_hash in ( select QueryStatsBaseTable.query_hash from sys.dm_exec_query_stats QueryStatsBaseTable where last_execution_time > DATEADD(hh,-@FilterHours,GETDATE()) group by query_hash having (sum(total_worker_time)/sum(execution_count))>@ConvertMiliSeconds and sum(execution_count)>@execution_count ) order by total_worker_time/execution_count DESC; if @debugFlg=1 select * from #FindTopCPUQueries_set1 order by QueryExecuted if object_id('tempdb..#FindTopCPUQueries_set2') is not null DROP TABLE [dbo].[#FindTopCPUQueries_set2] select servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted ,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime ,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName into #FindTopCPUQueries_set2 from #FindTopCPUQueries_set1 group by query_hash,servername,runtime order by AvgCPUTime desc select * from #FindTopCPUQueries_set2 order by AvgCPUTime desc --drop table #FindTopCPUQueries_set1 --drop table #FindTopCPUQueries_set2