DBA-常用到的动态视图分析语句

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