查询统计

数据库引擎接收到一个新的查询请求（Batch或SP），查询优化器会生成执行计划，并缓存到内存中；下次再次执行相同的查询请求时，数据库引擎从复用已经缓存的执行计划，换句话，数据库引擎为每一个查询请求生成执行计划，并把已经生成的执行计划缓存起来，当接收到相同的查询请求时，数据库引擎复用已缓存的执行计划。查询请求（Batch或SP）中的每一个查询语句的执行计划，都会被缓存到内存中，数据库引擎统计执行计划的性能参数，缓存在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

View Code

二，查看统计数据的平均值

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

View Code

对于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

View Code

缓存的查询计划，被数据库引擎缓存在视图：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

四，内存授予

内存授予只出现在特定的查询语句中，如果一个查询包含排序，Hash等操作，那么该语句在执行之前，必须申请到必需的内存，这意味着，如果数据库引擎不能分配足够的授予内存，那么查询请求将不会执行。

视图sys.dm_exec_query_memory_grants 返回当前数据库中申请内存授予的状态：

select mg.session_id
    --,mg.request_id
    ,mg.resource_semaphore_id
    ,mg.wait_time_ms
    ,mg.dop
    ,mg.requested_memory_kb
    ,mg.required_memory_kb
    ,mg.used_memory_kb
    ,mg.max_used_memory_kb
    ,mg.ideal_memory_kb
    ,st.text as entire_sql
from sys.dm_exec_query_memory_grants mg
outer apply sys.dm_exec_sql_text(mg.sql_handle) as st
order by mg.wait_time_ms desc

View Code

在申请内存授予时，数据库引擎会发送资源信号（resource semaphore），视图 sys.dm_exec_query_resource_semaphores 返回当前数据库中查询-执行的内存状态，用于检测当前数据库是否有足够的内存，用于所有的查询计划。

当内存紧张时，查询请求申请不到足够的授予内存，处于RESOURCE_SEMAPHORE等待状态。此时，数据库引擎发送资源信号（RESOURCE SEMAPHORE）申请授予内存（Requested Memory）。

当SQL Server实例接收到用户的查询请求时，查询优化器首先创建编译计划（Complied Plan），根据编译计划再创建执行计划（Execution Plan）。查询优化器在创建编译计划时，需要计算查询语句在执行时需要消耗的内存。用于执行查询语句的内存分为必需内存（Required Memory）和额外内存（Additional Memory），必需内存是指SQL Server实例执行Sort或Hash操作必须分配的最小内存，如果没有分配必需内存，查询请求不会执行；额外内存是查询语句用于存储临时的中间数据的内存，如果SQL Server没有足够的内存，数据库引擎把临时数据存储在硬盘中，这会降低查询语句执行的性能。

SQL Server 要授予多少内存，查询请求才能真正开始执行呢？

• Step1，计算需要的内存（Needed Memory）：SQL Server计算每个查询需要多少内存才能执行，这通常是必需内存和额外内存之和，当查询请求以并发方式执行时，需要的内存公式是：（Required Memory*DOP）+额外内存。
• Step2，计算请求的内存（Requested Memory）：SQL Server检查每个查询请求需要的内存数量是否超出系统的限制，SQL Server减少额外内存的数量，以致于不会超出系统的上限，这个最终的内存数量是查询语句得以执行的请求内存。
• Step3，为查询分配请求内存：SQL Server实例发送资源信号（RESOURCE SEMAPHORE），为查询（Query）语句授予/分配请求的物理内存。

当资源信号发送之后，如果SQL Server实例不能被授予查询的请求内存，那么查询请求处于RESOURCE_SEMAPHORE 等待状态。SQL Server维护一个先入先出（ first-come-first-served）的等待队列，当新的查询请求处于RESOURCE_SEMAPHORE 等待状态，SQL Server将该查询放入队列的末尾。一旦SQL Server实例找到足够的空闲内存，那么数据库引擎取出RESOURCE_SEMAPHORE 等待队列顶端的第一个查询，立即授予其申请的请求内存（Requested Memory）；该查询获得请求内存之后，开始执行查询任务。如果SQL Server实例长时间有查询处于RESOURCE_SEMAPHORE等待状态，说明SQL Server 面临内存压力。

参考文档：

Execution Related Dynamic Management Views and Functions (Transact-SQL)

• 还没有人评论，欢迎说说您的想法！