--语句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