查找占用upu 最多的一些脚本
/*------------------------------------------+ #| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : | #|{>/------------------------------------\<}| #|: | 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 tempdbgoIF object_id('tempdb..#FindTopCPUQueries_set1') is not null DROP TABLE [dbo].[#FindTopCPUQueries_set1]GOdeclare @ServerTime datetime = getdate(), @ConvertMiliSeconds bigint = 1000, @FilterMoreThanMiliSeconds bigint = 1, @FilterHours bigint = 2, @execution_count bigint = 2, @debugFlg bit = 0if @debugFlg=1 select @ServerTime as ServerTime, @ConvertMiliSeconds as ConvertMiliSeconds, @FilterMoreThanMiliSeconds as FilterMoreThanMiliSeconds, @FilterHours as FilterHours , @execution_count as execution_countselect 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_set1from sys.dm_exec_query_stats as QueryStatscross apply sys.dm_exec_sql_text(QueryStats.sql_handle) as QueryTextwhere 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 QueryExecutedif 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 ObjectNameinto #FindTopCPUQueries_set2from #FindTopCPUQueries_set1group by query_hash,servername,runtimeorder by AvgCPUTime descselect * from #FindTopCPUQueries_set2order by AvgCPUTime desc--drop table #FindTopCPUQueries_set1--drop table #FindTopCPUQueries_set2