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

  
  

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