• 有关查询和执行计划的DMV


    查看被缓存的查询计划

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 20
        st.text AS [SQL]
        , cp.cacheobjtype
        , cp.objtype
        , COALESCE(DB_NAME(st.dbid),
            DB_NAME(CAST(pa.value AS INT))+'*',
            'Resource') AS [DatabaseName]
        , cp.usecounts AS [Plan usage]
        , qp.query_plan
    FROM sys.dm_exec_cached_plans cp                      
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
    OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
    WHERE pa.attribute = 'dbid'
      AND st.text LIKE '%这里是查询语句包含的内容%'  

    结果是:

    1

    可以根据查询字段来根据关键字查看缓冲的查询计划。

    查看某一查询是如何使用查询计划的

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 20
      SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
      ((CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
      , qt.text AS [Parent Query]
      , DB_NAME(qt.dbid) AS DatabaseName
      , qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
      ((CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1)
    LIKE '%指定查询包含的字段%'  

    结果是:

    2

    查看数据库中跑的最慢的前20个查询以及它们的执行计划

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 20
      CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
                                         AS [Total Duration (s)]
      , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
                                   AS DECIMAL(28, 2)) AS [% CPU]
      , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
            qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
      , qs.execution_count
      , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count
                    AS DECIMAL(28, 2)) AS [Average Duration (s)]
      , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,     
        ((CASE WHEN qs.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2) + 1) AS [Individual Query
      , qt.text AS [Parent Query]
      , DB_NAME(qt.dbid) AS DatabaseName
      , qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
    ORDER BY qs.total_elapsed_time DESC                    

    3

    查看数据库中哪个查询最耗费资源有助于你解决问题

    被阻塞时间最长的前20个查询以及它们的执行计划

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 20
      CAST((qs.total_elapsed_time - qs.total_worker_time) /     
            1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)]
      , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
            AS DECIMAL(28,2)) AS [% CPU]
      , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
            qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
      , qs.execution_count
      , CAST((qs.total_elapsed_time  - qs.total_worker_time) / 1000000.0
        / qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)]
      , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,    
      ((CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
      , qt.text AS [Parent Query]
      , DB_NAME(qt.dbid) AS DatabaseName
      , qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
    ORDER BY [Total time blocked (s)] DESC                      

    结果如图:

    4

    找出这类查询也是数据库调优的必须品

    最耗费CPU的前20个查询以及它们的执行计划

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 20
      CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))
                                               AS [Total CPU time (s)]
      , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
                                          AS DECIMAL(28,2)) AS [% CPU]
      , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
               qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
                 , qs.execution_count
      , CAST((qs.total_worker_time) / 1000000.0
        / qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)]
      , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,     
        ((CASE WHEN qs.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
      , qt.text AS [Parent Query]
      , DB_NAME(qt.dbid) AS DatabaseName
      , qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
    ORDER BY [Total CPU time (s)] DESC         

    原理同上,就不上图了

    最占IO的前20个查询以及它们的执行计划

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 20
      [Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
      , [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
                                                qs.execution_count
      , qs.execution_count
      , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,     
      ((CASE WHEN qs.statement_end_offset = -1
        THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
        ELSE qs.statement_end_offset
        END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
      , qt.text AS [Parent Query]
      , DB_NAME(qt.dbid) AS DatabaseName
      , qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    ORDER BY [Total IO] DESC                                  

    结果如图:

    5

    能帮助找出占IO的查询

    查找被执行次数最多的查询以及它们的执行计划

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT TOP 20
        qs.execution_count
        , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,  
        ((CASE WHEN qs.statement_end_offset = -1
          THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
          ELSE qs.statement_end_offset
          END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
        , qt.text AS [Parent Query]
        , DB_NAME(qt.dbid) AS DatabaseName
        , qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    ORDER BY qs.execution_count DESC;    

    结果如图:

    6

    可以针对用的最多的查询语句做特定优化。

    特定语句的最后运行时间

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SELECT DISTINCT TOP 20
        qs.last_execution_time
        , qt.text AS [Parent Query]
        , DB_NAME(qt.dbid) AS DatabaseName
    FROM sys.dm_exec_query_stats qs                      
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    WHERE qt.text LIKE '%特定语句的部分%'
    ORDER BY qs.last_execution_time DESC            

    结果如图:

    7

  • 相关阅读:
    Reactor系列(十一)take获取
    Reactor系列(十)collectMap集合
    Reactor系列(九)collect集合
    C++线程安全队列
    C++编辑编译链接运行
    C++函数的返回值——返回引用类型&,非引用类型
    关于通过在代码中执行shell脚本启动其他应用方法
    linux指令中单杆和双杠区别
    关于浏览器屏蔽掉百度右侧广告热搜的方法
    C#异步方法 async、await用法和解析
  • 原文地址:https://www.cnblogs.com/CareySon/p/2506035.html
Copyright © 2020-2023  润新知