• SQL Server性能监控常用语句


      1 1、查找目前SQL Server所执行的SQL语法,并展示资源情况:
     33 SELECT  s2.dbid ,
     34          DB_NAME(s2.dbid) AS [数据库名] ,
     35          --s1.sql_handle ,
     36          ( SELECT TOP 1
     37                      SUBSTRING(s2.text, statement_start_offset / 2 + 1,
     38                                ( ( CASE WHEN statement_end_offset = -1
     39                                         THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
     40                                                * 2 )
     41                                         ELSE statement_end_offset
     42                                    END ) - statement_start_offset ) / 2 + 1)
     43          ) AS [语句] ,
     44          execution_count AS [执行次数] ,
     45          last_execution_time AS [上次开始执行计划的时间] ,
     46          total_worker_time AS [自编译以来执行所用的 CPU 时间总量(微秒)] ,
     47          last_worker_time AS [上次执行计划所用的 CPU 时间(微秒)] ,
     48          min_worker_time AS [单次执行期间曾占用的最小 CPU 时间(微秒)] ,
     49          max_worker_time AS [单次执行期间曾占用的最大 CPU 时间(微秒)] ,
     50          total_logical_reads AS [总逻辑读] ,
     51          last_logical_reads AS [上次逻辑读] ,
     52          min_logical_reads AS [最少逻辑读] ,
     53          max_logical_reads AS [最大逻辑读] ,
     54          total_logical_writes AS [总逻辑写] ,
     55          last_logical_writes AS [上次逻辑写] ,
     56          min_logical_writes AS [最小逻辑写] ,
     57          max_logical_writes AS [最大逻辑写]
     58  FROM    sys.dm_exec_query_stats AS s1
     59          CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
     60  WHERE   s2.objectid IS NULL
     61  ORDER BY last_worker_time DESC
     62 
     63 2、展示耗时查询:
     96 -- List expensive queries 
     97  DECLARE @MinExecutions int; 
     98  SET @MinExecutions = 5 
     99    
    100  SELECT EQS.total_worker_time AS TotalWorkerTime 
    101        ,EQS.total_logical_reads + EQS.total_logical_writes AS TotalLogicalIO 
    102        ,EQS.execution_count As ExeCnt 
    103        ,EQS.last_execution_time AS LastUsage 
    104        ,EQS.total_worker_time / EQS.execution_count as AvgCPUTimeMiS 
    105        ,(EQS.total_logical_reads + EQS.total_logical_writes) / EQS.execution_count  
    106         AS AvgLogicalIO 
    107        ,DB.name AS DatabaseName 
    108        ,SUBSTRING(EST.text 
    109                  ,1 + EQS.statement_start_offset / 2 
    110                  ,(CASE WHEN EQS.statement_end_offset = -1  
    111                         THEN LEN(convert(nvarchar(max), EST.text)) * 2  
    112                         ELSE EQS.statement_end_offset END  
    113                   - EQS.statement_start_offset) / 2 
    114                  ) AS SqlStatement 
    115        -- Optional with Query plan; remove comment to show, but then the query takes !!much longer time!! 
    116        --,EQP.[query_plan] AS [QueryPlan] 
    117  FROM sys.dm_exec_query_stats AS EQS 
    118       CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST 
    119       CROSS APPLY sys.dm_exec_query_plan(EQS.plan_handle) AS EQP 
    120       LEFT JOIN sys.databases AS DB 
    121           ON EST.dbid = DB.database_id      
    122  WHERE EQS.execution_count > @MinExecutions 
    123        AND EQS.last_execution_time > DATEDIFF(MONTH, -1, GETDATE()) 
    124  ORDER BY AvgLogicalIo DESC 
    125          ,AvgCPUTimeMiS DESC
    126 
    127 3、当前进程及其语句:
    149 -- Current processes and their SQL statements 
    150  SELECT PRO.loginame AS LoginName 
    151        ,DB.name AS DatabaseName 
    152        ,PRO.[status] as ProcessStatus 
    153        ,PRO.cmd AS Command 
    154        ,PRO.last_batch AS LastBatch 
    155        ,PRO.cpu AS Cpu 
    156        ,PRO.physical_io AS PhysicalIo 
    157        ,SES.row_count AS [RowCount] 
    158        ,STM.[text] AS SQLStatement 
    159  FROM sys.sysprocesses AS PRO 
    160       INNER JOIN sys.databases AS DB 
    161           ON PRO.dbid = DB.database_id 
    162       INNER JOIN sys.dm_exec_sessions AS SES 
    163          ON PRO.spid = SES.session_id 
    164       CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM      
    165  WHERE PRO.spid >= 50  -- Exclude system processes 
    166  ORDER BY PRO.physical_io DESC 
    167          ,PRO.cpu DESC;
    168 
    169 4、存储过程执行情况:
    186 -- Stored Procedure Execution Statistics 
    187  SELECT ISNULL(DBS.name, '') AS DatabaseName 
    188        ,OBJECT_NAME(EPS.object_id, EPS.database_id) AS ObjectName 
    189        ,EPS.cached_time AS CachedTime 
    190        ,EPS.last_elapsed_time AS LastElapsedTime 
    191        ,EPS.execution_count AS ExecutionCount 
    192        ,EPS.total_worker_time / EPS.execution_count AS AvgWorkerTime 
    193        ,EPS.total_elapsed_time / EPS.execution_count AS AvgElapsedTime 
    194        ,(EPS.total_logical_reads + EPS.total_logical_writes) 
    195         / EPS.execution_count AS AvgLogicalIO 
    196  FROM sys.dm_exec_procedure_stats AS EPS 
    197       LEFT JOIN sys.databases AS DBS 
    198           ON EPS.database_id = DBS.database_id 
    199  ORDER BY AvgWorkerTime DESC;
    200 
    201 5、开销较大的查询:
    225 /*
    226  开销较大的查询
    227  */
    228  SELECT  ss.SUM_execution_count ,
    229          t.text ,
    230          ss.SUM_total_elapsed_time ,
    231          ss.sum_total_worker_time ,
    232          ss.sum_total_logical_reads ,
    233          ss.sum_total_logical_writes
    234  FROM    ( SELECT    S.plan_handle ,
    235                      SUM(s.execution_count) SUM_Execution_count ,
    236                      SUM(s.total_elapsed_time) SUM_total_elapsed_time ,
    237                      SUM(s.total_worker_time) SUM_total_worker_time ,
    238                      SUM(s.total_logical_reads) SUM_total_logical_reads ,
    239                      SUM(s.total_logical_writes) SUM_total_logical_writes
    240            FROM      sys.dm_exec_query_stats s
    241            GROUP BY  S.plan_handle
    242          ) AS ss
    243          CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
    244  ORDER BY sum_total_logical_reads DESC  
    245   
  • 相关阅读:
    服务返返回状态码详解
    LeetCode#28 Implement strStr()
    LeetCode#58 Length of Last Word
    LeetCode#66 Plus One
    spooling技术
    文件的打开与关闭
    DMA方式与通道方式
    中断向量、向量中断、向量地址
    中断响应优先级和中断处理优先级
    I/O接口
  • 原文地址:https://www.cnblogs.com/liyangLife/p/4626063.html
Copyright © 2020-2023  润新知