檢測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