/*------------------------------------------+ 
#| = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = : = :  | 
#|{>/------------------------------------\<}| 
#|: | 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