性能调优11：查询统计

数据库引擎的工作流程可以归纳为接收请求、执行请求和返回结果。数据库引擎每接收到一个新的查询请求（Query Request），查询优化器就会编译请求、生成执行计划，并把执行计划缓存到内存中，执行计划，最后向客户端返回查询的结果。缓存执行计划的目的是为了复用，减少编辑查询请求的时间消耗和CPU消耗，当数据库引擎再次接收到相同的查询请求，数据库引擎就复用已经缓存的执行计划。

对于计划缓存，数据库引擎并不是永久保存在内存中，而是会根据内存压力，智能剔除一些创建时间早，而且复用频次少的执行计划。

数据库引擎会把每一个查询请求的执行信息保存起来，例如，查询的文本，查询等待的时长，执行的时间，消耗的资源等，并对这些信息进行汇总和统计，这些汇总之后的数据就是查询统计，存储到内存结构 DMV：sys.dm_exec_query_stats中。在该视图中，每一行数据都表示一个查询语句的统计数据：

• sql_handle：用以唯一标识一个TSQL文本（Batch或SP），TSQL文本存储在SQL Manager Cache（SQLMGR）中；
• plan_handle：用于唯一标识一个已编辑的查询计划，查询计划存储在计划缓存中；

一个sql_handle 能够生成多个查询计划，对应多个plan_handle，但是每个plan_handle只能对应一个sql_handle 。

一，获取查询语句

视图sys.dm_exec_query_stats 缓存的是单个查询语句的执行计划，而sql_handler引用的是整个TSQL文本（Batch或SP），为了获得单个查询语句的文本，必须通过语句的偏移字段来抽取，偏移量是字节，字节数量从0开始：

• statement_start_offset：语句开始偏移的字节序号
• statement_end_offset：语句结束偏移的字节序号，-1 表示TSQL文本的末尾；

由于函数 sys.dm_exec_sql_text 返回的TSQL文本是以nvarchar(max)类型存储的，一般情况下，字节偏移量都是2的倍数，获取查询语句的脚本是：

select substring(st.text 
                ,qs.statement_start_offset/2+1,
                ( case when qs.statement_end_offset = -1 
                            then len(convert(nvarchar(max), st.text))
                        else (qs.statement_end_offset - qs.statement_start_offset)/2
                    end 
                )
        ) as individual_query
        ,st.text as entire_query
from sys.dm_exec_query_stats qs
outer apply sys.dm_exec_sql_text(qs.sql_handle) as st

二，查看统计数据

所有请求的执行信息，都存储到DMV：sys.dm_exec_query_stats中，需要对该数据做一些统计变化，找出对性能影响最大的查询请求，由于该DMV存储的是累积值，要关注数据开始记录的时间，并关注每个查询请求执行的平均值。

1，查看语句级别的统计数据

执行计划的重编译次数，执行查询的总时间，逻辑读和物理读的次数等计数器，是观察查询执行情况的重要指标：

• plan_generation_num：表示执行计划产生的数量，表示同一个TSQL文本重新编译的次数；
• total_elapsed_time：单词elapsed是指单个语句执行的总时间，包括 waiting的时间或 CPU工作（worker）的时间；
• total_logical_reads：查询计划完成的逻辑读的次数；
• total_physical_reads：查询计划完成的物理读的次数；

以下脚本用于查看语句级别的执行计划的平均数据，并按照平均执行时间排序：

select top 111 
    qs.execution_count,
    qs.total_rows/qs.execution_count as avg_rows,
    qs.total_worker_time/qs.execution_count/1000 as avg_worker_ms,
    qs.total_elapsed_time/qs.execution_count/1000 as avg_elapsed_ms,
    qs.total_physical_reads/qs.execution_count as avg_physical_reads,
    qs.total_logical_reads/qs.execution_count as avg_logical_reads,
    qs.total_logical_writes/qs.execution_count as avg_logical_writes,
    qs.creation_time,
    qs.plan_generation_num,
    --st.text as entire_query,
    substring(st.text,
            qs.statement_start_offset/2 + 1,      
            ( case when qs.statement_end_offset = -1 
                        then len(convert(nvarchar(max), st.text))
                else (qs.statement_end_offset -qs.statement_start_offset)/2
              end)
            ) as individual_query
from sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 
order by avg_elapsed_ms desc

2，查看存储过程级别的查询统计

对于缓存的存储过程，数据库引擎把SP相关的统计数据缓存在视图：sys.dm_exec_procedure_stats 中，每一行数据都表示一个SP的统计数据：

select top 111
    db_name(ps.database_id) as db_name
    ,ps.database_id
    ,object_schema_name(ps.object_id,ps.database_id)+‘.‘+object_name(ps.object_id,ps.database_id) as proc_name
    ,ps.type_desc as proc_type
    ,ps.cached_time
    ,ps.execution_count
    ,ps.total_worker_time/ps.execution_count/1000 as avg_worker_ms
    ,ps.total_elapsed_time/ps.execution_count/1000 as avg_elapsed_ms
    ,ps.total_physical_reads/ps.execution_count as avg_physical_reads
    ,ps.total_logical_reads/ps.execution_count as avg_logical_reads
    ,ps.total_logical_writes/ps.execution_count as avg_logical_writes
from sys.dm_exec_procedure_stats ps
where ps.database_id<32767
order by avg_elapsed_ms desc

对于database_id 为 32767，这个id是资源数据库（Resource Database）预留的ID，一般情况下，用户创建的数据库ID都会小于该数值。

三，查看查询计划

函数 sys.dm_exec_query_plan 以XML格式返回指定batch或SP的查询计划，参数是plan_handle，这意味着，函数返回的是整个语句（Batch或SP）的执行计划，而视图sys.dm_exec_query_stats 缓存的是Batch或SP中某一个查询语句的统计信息，在query_plan字段上会出现大量的冗余：

select top 111 
    qs.execution_count,
    qs.total_rows/qs.execution_count as avg_rows,
    qs.total_worker_time/qs.execution_count/1000 as avg_worker_ms,
    qs.total_elapsed_time/qs.execution_count/1000 as avg_elapsed_ms,
    qs.total_physical_reads/qs.execution_count as avg_physical_reads,
    qs.total_logical_reads/qs.execution_count as avg_logical_reads,
    qs.total_logical_writes/qs.execution_count as avg_logical_writes,
    qs.creation_time,
    qs.plan_generation_num,
    st.text as entire_query,
    substring(st.text,
            qs.statement_start_offset/2 + 1,      
            ( case when qs.statement_end_offset = -1 
                        then len(convert(nvarchar(max), st.text))
                else (qs.statement_end_offset -qs.statement_start_offset)/2
              end)
            ) as individual_query,
    qp.query_plan
from sys.dm_exec_query_stats qs 
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st 
outer apply sys.dm_exec_query_plan(qs.plan_handle) as qp
order by avg_elapsed_ms desc

缓存的查询计划，被数据库引擎缓存在视图：sys.dm_exec_cached_plans，每一个查询计划都存储一行，从该视图中能够查看缓存的查询计划及其文本，计划占用的内存大小，以及查询计划被重用的次数等数据：

select cp.refcounts
    ,cp.usecounts
    ,cp.size_in_bytes
    ,cp.cacheobjtype
    ,cp.objtype
    ,st.text as entire_sql
    --,cp.plan_handle
from sys.dm_exec_cached_plans cp
outer apply sys.dm_exec_sql_text(cp.plan_handle) st

 

参考文档：