检测Sql Server服务器SQL语句执行情况

1、查找目前SQL Server所执行的SQL语法，并展示资源情况：

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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、展示耗时查询：

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

-- 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、当前进程及其语句：

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

-- 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、存储过程执行情况：

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- 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、开销较大的查询：

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

6,  查询某个SPID，session_id对应的执行sql.

SQL code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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