Sql Server执行情况

--- 1、查找目前SQL Server所执行的SQL语法，并展示资源情况： 
SELECT  s2.dbid ,
          DB_NAME(s2.dbid) AS [数据库名] ,
          --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 [语句] ,
          execution_count AS [执行次数] ,
          last_execution_time AS [上次开始执行计划的时间] ,
          total_worker_time AS [自编译以来执行所用的 CPU 时间总量（微秒）] ,
          last_worker_time AS [上次执行计划所用的 CPU 时间（微秒）] ,
          min_worker_time AS [单次执行期间曾占用的最小 CPU 时间（微秒）] ,
          max_worker_time AS [单次执行期间曾占用的最大 CPU 时间（微秒）] ,
          total_logical_reads AS [总逻辑读] ,
          last_logical_reads AS [上次逻辑读] ,
          min_logical_reads AS [最少逻辑读] ,
          max_logical_reads AS [最大逻辑读] ,
          total_logical_writes AS [总逻辑写] ,
          last_logical_writes AS [上次逻辑写] ,
          min_logical_writes AS [最小逻辑写] ,
          max_logical_writes AS [最大逻辑写]
  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 last_worker_time DESC

 

-- 2、展示耗时查询
 -- List expensive queries 
  DECLARE @MinExecutions int; 
  SET @MinExecutions = 5 
    
  SELECT EQS.total_worker_time AS TotalWorkerTime 
        ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO 
        ,EQS.execution_count As ExeCnt 
        ,EQS.last_execution_time AS LastUsage 
        ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS 
        ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count  
         AS AvgLogicalIO 
        ,DB.name AS DatabaseName 
        ,SUBSTRING(EST.text 
                  ,1 + EQS.statement_start_offset / 2 
                  ,(CASE WHEN EQS.statement_end_offset = -1  
                         THEN LEN(convert(nvarchar(max), EST.text)) * 2  
                         ELSE EQS.statement_end_offset END  
                   - EQS.statement_start_offset) / 2 
                  ) AS SqlStatement 
        -- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!! 
        --,EQP.[query_plan] AS [QueryPlan] 
  FROM sys.dm_exec_query_stats AS EQS 
       CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST 
       CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP 
       LEFT JOIN sys.databases AS DB 
           ON EST.dbid = DB.database_id      
  WHERE EQS.execution_count > @MinExecutions 
        AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE()) 
  ORDER BY AvgLogicalIo DESC 
          ,AvgCPUTimeMiS DESC

 

-- 3、当前进程及其语句：
-- Current processes and their SQL statements 
  SELECT PRO.loginame AS LoginName 
        ,DB.name AS DatabaseName 
        ,PRO.[status] as ProcessStatus 
        ,PRO.cmd AS Command 
        ,PRO.last_batch AS LastBatch 
        ,PRO.cpu AS Cpu 
        ,PRO.physical_io AS PhysicalIo 
        ,SES.row_count AS [RowCount] 
        ,STM.[text] AS SQLStatement 
  FROM sys.sysprocesses AS PRO 
       INNER JOIN sys.databases AS DB 
           ON PRO.dbid = DB.database_id 
       INNER JOIN sys.dm_exec_sessions AS SES 
          ON PRO.spid = SES.session_id 
       CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM      
  WHERE PRO.spid >= 50  -- Exclude system processes 
  ORDER BY PRO.physical_io DESC 
          ,PRO.cpu DESC;

 -- 4、存储过程执行情况： Stored Procedure Execution Statistics 
  SELECT ISNULL(DBS.name, '') AS DatabaseName 
        ,OBJECT_NAME(EPS.object_id, EPS.database_id) AS ObjectName 
        ,EPS.cached_time AS CachedTime 
        ,EPS.last_elapsed_time AS LastElapsedTime 
        ,EPS.execution_count AS ExecutionCount 
        ,EPS.total_worker_time / EPS.execution_count AS AvgWorkerTime 
        ,EPS.total_elapsed_time / EPS.execution_count AS AvgElapsedTime 
        ,(EPS.total_logical_reads + EPS.total_logical_writes) 
         / EPS.execution_count AS AvgLogicalIO 
  FROM sys.dm_exec_procedure_stats AS EPS 
       LEFT JOIN sys.databases AS DBS 
           ON EPS.database_id = DBS.database_id 
  ORDER BY AvgWorkerTime DESC;

--5 
/*
  开销较大的查询
  */
  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  

--查询某个SPID，session_id对应的执行sql.
select er.session_id, cast(csql.text AS varchar(255)) AS CallingSQL

from master.sys.dm_exec_requests er WITH (NOLOCK)

 CROSS APPLY fn_get_sql (er.sql_handle) csql

where er.session_id =309

【感谢转自https://www.cnblogs.com/cxd4321/p/4095537.html】