数据库引擎接收到一个新的查询请求（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
二，查看统计数据的平均值
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
四，内存授予
内存授予只出现在特定的查询语句中，如果一个查询包含排序，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
在申请内存授予时，数据库引擎会发送资源信号（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)
