性能调优11：查询统计
数据库引擎的工作流程可以归纳为接收请求、执行请求和返回结果。数据库引擎每接收到一个新的查询请求（Query Request），查询优化器就会执行以下工作流程：
- 编译请求：对TSQL语句进行语法解析，编译请求，生成TSQL语句表示的逻辑结构。
- 查询优化：根据TSQL语句的逻辑结构，生成多个预估的执行方案，并根据统计信息，评估每个预估方案的开销，选择开销最低的方案作为最优方案。
- 执行计划：根据最优方案生成执行计划，也就是把TSQL语句中的逻辑操作符转变为物理操作符，把执行计划传递给存储引擎，并把执行计划缓存到内存中。
- 响应请求：存储引擎执行查询计划，记录每个查询的执行信息，最后把查询的结果返回到客户端。
把执行计划存储到内存的目的是为了复用执行计划，减少编译查询请求的时间消耗和CPU消耗。当数据库引擎再次接收到相同或相似的查询请求时，数据库引擎探测到该请求的执行计划已经被缓存，那么就会跳过编译请求的过程，直接复用已经缓存的执行计划。
数据库引擎并不是把查询计划永久保存在内存中，而是会根据内存的压力，智能地剔除一些创建时间早、复用频次少的执行计划。为了实现计划缓存的精准清理，数据库引擎需要对查询和查询计划进行定位和统计，定位通过请求的语句句柄和计划句柄来实现，清理通过查询统计来实现。
一，语句句柄和计划句柄
数据库引擎要实现查询计划的复用，必须能够识别查询已经执行过，这就需要对查询语句进行标记；查询的执行计划也会被标记，这就需要用到两个唯一值：
- sql_handle：用以唯一标识一段TSQL文本（Batch或SP），TSQL文本存储在SQL Manager Cache（SQLMGR）中。
- plan_handle：用于唯一标识一个已编辑的查询计划，查询计划存储在计划缓存（Plan Cache）中。
sql_handle和plan_handle是如何生成的？
- 对于ad hoc查询，sql_handle是基于整体的SQL Text生成的哈希值；如果一个batch包含多个TSQL语句，那么多个TSQL语句作为一个整体，batch中的查询字句拥有相同的sql_handle值，但是有不同的偏移量。
- 对于执行的SP、触发器或函数等数据库对象，sql_handle是由database ID 和 object ID 派生的哈希值。
- plan_handle是由整体（批处理或SP）生成的已编译计划派生的哈希值。
sql_handle和plan_handle 之间具有1对多的关系。一个sql_handle 能够生成多个查询计划，对应多个plan_handle，但是每个plan_handle只能对应一个sql_handle 。sql_handle对于每一个batch都是唯一的，但是，如果执行batch的条件发生改变，比如set 选项发生变化，那么数据库引擎在执行同样的batch时，会生成新的执行计划，产生新的plan_handle，但是sql_handle不变。想要了解更详细的信息，请阅读《2.0 Sql_Handle and Plan_Handle Explained》。
1，SQL句柄
sql_handle是一个token，用于唯一标记查询文本所属的batch或sp，把sql_handle传递给 sys.dm_exec_sql_text()动态管理函数，并结合偏移 statement_start_offset和statement_end_offset，可以抽取出单个查询的SQL文本。
函数 sys.dm_exec_sql_text(sql_handle | plan_handle)用于获得整个Batch的TSQL文本，由于TSQL文本都是以nvarchar(max)类型存储的，一个nvarchar是2个字节，因此，一般情况下，字节偏移量都是2的倍数。
2，计划句柄
plan_handle是一个token，是整个Execution Plan的哈希值，用于唯一标识一个batch或sp的执行计划，把plan_handle传递给sys.dm_exec_query_plan(plan_handle)动态管理函数，可以获取整体（batch或sp）的showplan。
3，查询计划（query plan）
查询计划是指查询语句的显示计划（showplan），动态管理视图 sys.dm_exec_query_plan 返回以XML格式表示的showplan，它只能返回整个batch执行的showplan，不能单独查看某一个子句的执行计划。要想查看单个子句的执行计划，可以通过动态管理视图 sys.dm_exec_text_query_plan 来实现，该视图返回以文本格式表示的showplan：
sys.dm_exec_query_plan(plan_handle) sys.dm_exec_text_query_plan ( plan_handle , { statement_start_offset | 0 | DEFAULT } , { statement_end_offset | -1 | DEFAULT } )
对于文本查询计划，需要指定特定的语句的偏移statement_start_offset 和 statement_end_offset，才能显示单个子句的showplan。
二，抽取查询语句
动态管理视图 sys.dm_exec_query_stats 缓存的是单个查询语句的执行计划，而sql_handle指向的是整个Batch或SP的句柄值，因此，在该视图中，可能存在多个相同的sql_handle。
为了获得单个查询语句的文本，必须通过偏移量从整体（Batch语句）中抽取，偏移量的单位是字节，字节数量从0开始：
- statement_start_offset：语句开始偏移的字节序号
- statement_end_offset：语句结束偏移的字节序号，-1 表示TSQL文本的末尾
把sql_handle传递给 sys.dm_exec_sql_text()动态管理函数，并结合偏移 statement_start_offset和statement_end_offset，可以抽取出单个查询的SQL文本，抽取查询语句的脚本是：
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：sys.dm_exec_query_stats中，从该统计数据中，可以找出对性能影响最大的查询请求，由于该DMV存储的是累加值，在使用数据之前，一定要关注记录的开始时间：
- creation_time：计划编译的时间
- last_execution_time：最近一次计划开始执行的时间
这两个时间表示查询计划的第一次执行和最后一次执行的时间戳。
1，查看语句级别的统计数据
执行计划的重编译次数，执行查询的总时间，逻辑读和物理读的次数等计数器，是观察查询执行情况的重要指标：
- plan_generation_num：表示执行计划产生的数量，表示同一个TSQL文本重新编译的次数；
- creation_time ：计划编译的时间
- execution_count：计划执行的次数
- total_elapsed_time和max_elapsed_time：查询计划完成的总时间，单词elapsed是指单个语句执行的总时间，包括 waiting的时间或 CPU工作（worker）的时间，单位是微秒（us），一微秒是千分之一毫秒(ms)
- total_worker_time 和 max_worker_time：CPU工作的总时间和最大时间，单位是微秒（us）
- total_logical_reads和max_logical_reads：查询计划执行的逻辑读的总次数；
- total_logical_writes和max_logical_writes：查询计划执行的逻辑写的总次数；
- total_physical_reads和 max_physical_reads：查询计划执行的物理读的总次数；
- total_rows和max_rows：查询返回的数据行的总数量
- total_dop和max_dop：并发执行的并发度的累加和
- total_grant_kb和max_grant_kb：该查询计划收到的预留授予内存（reserved memory grant）的总量，单位是KB
- total_used_grant_kb和max_used_grant_kb：该查询计划使用的预留授予内存（reserved memory grant）的总量，单位是KB
- total_ideal_grant_kb：该查询计划预估的理想授予内存（ideal memory grant）的总量，单位是KB
- total_splils、max_spills和min_spills：查询计划在完成一次执行时，出现页溢出的总页数；
以下脚本用于查看执行计划在单个语句级别上的平均数据，并按照平均执行时间排序，获取 top 111 的数据：
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）的showplan，XML格式是可视化的，也可以返回文本格式的showplan。
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 batch_sql --,cp.plan_handle from sys.dm_exec_cached_plans cp outer apply sys.dm_exec_sql_text(cp.plan_handle) st
六，存储过程的统计信息
对于缓存的存储过程，sys.dm_exec_procedure_stats 返回聚合的性能统计数据，每一行都代表一个缓存的存储过程计划，
select db_name(p.database_id) as dbname ,o.name as proc_name ,p.type_desc ,p.cached_time ,p.execution_count ,p.total_worker_time ,p.max_worker_time ,p.total_physical_reads ,p.max_physical_reads ,p.total_logical_writes ,p.max_logical_writes ,p.total_logical_reads ,p.max_logical_reads ,p.total_elapsed_time ,p.max_elapsed_time ,p.total_spills ,p.min_spills ,p.max_spills ,p.plan_handle ,cp.bucketid ,cp.refcounts ,cp.usecounts ,cp.size_in_bytes ,pn.query_plan from sys.dm_exec_procedure_stats p inner join sys.objects o on p.object_id=o.object_id inner join sys.dm_exec_cached_plans as cp on p.plan_handle=cp.plan_handle outer apply sys.dm_exec_query_plan(p.plan_handle) as pn
参考文档：
Execution Related Dynamic Management Views and Functions (Transact-SQL)
