--語句1：獲取前20邏輯讀取次數或邏輯寫入次數或CPU 時間 SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC -- 邏輯讀取次數 --ORDER BY qs.total_logical_writes DESC -- 邏輯寫入次數 --ORDER BY qs.total_worker_time DESC -- CPU 時間 --語句2：獲取前20執行的 SP 命令的總工作時間 （CPU 壓力） SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.execution_count AS 'Execution Count', ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second', ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- 當前資料庫 ORDER BY qs.total_worker_time DESC --語句3： 獲取前20 執行的 SP 命令邏輯寫入/分鐘 SELECT TOP 20 qt.text AS 'SP Name', qs.total_logical_writes, qs.total_logical_writes/qs.execution_count AS 'AvgLogicalWrites', qs.total_logical_writes/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Logical Writes/Min', qs.execution_count AS 'Execution Count', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- 當前資料庫 ORDER BY qs.total_logical_writes DESC --語句4： 獲取前20執行的 SP 命令的邏輯讀取（記憶體壓力） SELECT TOP 20 qt.text AS 'SP Name', total_logical_reads, qs.execution_count AS 'Execution Count', total_logical_reads/qs.execution_count AS 'AvgLogicalReads', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.total_logical_writes, qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- 當前資料庫 ORDER BY total_logical_reads DESC --語句5： 獲取前20執行的 SP 命令由物理讀取 （讀取 I/O 壓力） SELECT TOP 20 qt.text AS 'SP Name', qs.total_physical_reads, qs.total_physical_reads/qs.execution_count AS 'Avg Physical Reads', qs.execution_count AS 'Execution Count', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache', qt.dbid FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- 當前資料庫 ORDER BY qs.total_physical_reads DESC --語句6： 獲取前20執行的 SP 命令執行計數 SELECT TOP 20 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY qs.execution_count DESC
確定長時間執行的事務
SELECT ST.transaction_id AS TransactionID , DB_NAME(DT.database_id) AS DatabaseName , AT.transaction_begin_time AS TransactionStartTime , DATEDIFF(SECOND, AT.transaction_begin_time, GETDATE()) AS TransactionDuration , CASE AT.transaction_type WHEN 1 THEN 'Read/Write Transaction' WHEN 2 THEN 'Read-Only Transaction' WHEN 3 THEN 'System Transaction' WHEN 4 THEN 'Distributed Transaction' END AS TransactionType , CASE AT.transaction_state WHEN 0 THEN 'Transaction Not Initialized' WHEN 1 THEN 'Transaction Initialized & Not Started' WHEN 2 THEN 'Active Transaction' WHEN 3 THEN 'Transaction Ended' WHEN 4 THEN 'Distributed Transaction Initiated Commit Process' WHEN 5 THEN 'Transaction in Prepared State & Waiting Resolution' WHEN 6 THEN 'Transaction Committed' WHEN 7 THEN 'Transaction Rolling Back' WHEN 8 THEN 'Transaction Rolled Back' END AS TransactionState FROM sys.dm_tran_session_transactions AS ST INNER JOIN sys.dm_tran_active_transactions AS AT ON ST.transaction_id = AT.transaction_id INNER JOIN sys.dm_tran_database_transactions AS DT ON ST.transaction_id = DT.transaction_id ORDER BY TransactionStartTime GO