授予内存-阿里云开发者社区

授予内存（Grant Memory）是专门用于执行排序操作和哈希操作的内存，由于排序操作（Sort）需要临时存储排序的中间结果集，哈希连接（Hash Join）和哈希聚合（Hash Aggregation）需要创建临时的哈希表，这些数据都需要全部缓存或部分缓存到内存中，因此，在查询请求（Request）真正执行之前，执行计划必须向系统申请一定数量的内存资源，这些内存资源叫做授予内存。如果SQL Server引擎不能给该执行计划分配其申请的授予内存，那么它不会开始执行，处于等待资源信号（RESOURCE_SEMAPHORE）的状态。

SQL Server使用资源信号标（Resource Semaphore）追踪系统分配给查询计划的授予内存的数量，资源信号工作的流程是：数据库引擎接收到一个查询请求（Request），该查询请求包含排序或哈希操作，因此，需要申请授予内存，如果，SQL Server引擎用完了内存空间，此时，SQL Server引擎利用资源信号，使查询请求转变为等待内存变为可用的状态，这样做的目的是使该执行计划不会因为内存不足而抛出错误。等到其他查询请求释放内存，SQL Server引擎拥有足够的内存之后，SQL Server引擎再把内存分配给查询请求，真正开始执行该查询请求。

RESOURCE_SEMAPHORE: occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.

授予内存（Granted Memory），查询执行保留（QE Reservations），查询执行内存（Query Execution Memory），工作内存（Workspace memory），内存保留（Memory Reservations），这些复杂的概念，归根到底都在说同一个内存消耗场景：在执行排序，哈希，创建索引，大容量复制（Bulk Copy）操作时，查询请求需要内存临时存储中间结果。

在一个查询请求的生命周期内，它可能需要申请不同的内存，这取决于查询请求做的是什么操作，例如，数据库引擎接收到一个查询请求，

首先数据库引擎需要解析和编译查询语句，它需要分配用于编译或优化的内存；一旦查询请求编译成功，这部分内存被释放。
然后，生成的执行计划被缓存到内存中，这部分缓存称作计划缓存（Plan Cache），SQL Server会保持查询请求的计划缓存，直到SQL Server重启，内存不足，或者执行计划被重编译。
最后，如果执行计划需要执行排序操作或哈希操作（哈希连接或哈希聚合），那么执行计划需要向系统申请用于存储临时排序的结果集或哈希表的内存，这部分内存称作授予内存。
注意：如果中间结果过大，SQL Server执行引擎会把部分数据转存到tempdb（硬盘）中，这样，中间数据就同时存储在内存和硬盘两个地方。

内存保留（Memory Reservations）或者查询执行保留（Query Execution (QE) Reservations）是查询计划执行前向系统请求保留的内存。当一个查询需要内存执行排序或哈希操作时，它会基于原始的查询计划（包含排序和哈希操作）向系统发送保留内存的请求（reservation request ），然后，当查询开始执行时，它请求内存，SQL Server授予内存。有一个内存书记 MEMORYCLERK_SQLQERESERVATIONS 用于记录分配的QE保留内存。

内存授予（Memory Grants）是一个正在执行的查询请求被系统授予的、用于执行排序或哈希操作的内存。如果查询请求向系统申请授予内存，系统却没有足够的内存时，查询请求必须等待，直到系统分配足够的授予内存。

一，授予内存

如果一个查询请求需要等待分配授予内存，那么可以通过系统视图：sys.dm_exec_query_memory_grants 查看等待系统分配授予内存的查询请求（Request），及它的关于授予内存的信息。如果一个查询请求不需要等待分配授予内存，或者不需要授予内存，那么它不会出现在该视图中。在编译查询请求时，SQL Server首先评估内存的使用量（ideal_memory_kb），查询计划在执行之前，需要向SQL Server申请内存（requested_memory_kb），SQL Server 根据系统系统内存的使用情况，分配一定数量的物理内存（granted_memory_kb）给该查询请求，查询请求获得授予内存之后开始“真正地”执行。

select 
    g.session_id,
    g.request_time,
    g.grant_time,
    g.wait_time_ms,
    g.query_cost,
    g.dop,
    g.requested_memory_kb,
    g.granted_memory_kb,
    g.required_memory_kb,
    g.used_memory_kb,
    g.max_used_memory_kb,
    g.ideal_memory_kb,
    g.wait_order,
    g.is_next_candidate,
    g.group_id,
    g.pool_id,
    g.resource_semaphore_id,
    st.text,
    p.query_plan
from sys.dm_exec_query_memory_grants g
outer apply sys.dm_exec_sql_text(g.sql_handle) as st
outer apply sys.dm_exec_query_plan(g.plan_handle) as p

View Code

视图：sys.dm_exec_requests 的字段：granted_query_memory ，用于表示为该查询请求已经分配的授予内存页的数量，如果一个查询请求正在等待授予内存，那么字段wait_type是RESOURCE_SEMAPHORE。

二，查看资源信号的汇总数据

通过系统视图：sys.dm_exec_query_resource_semaphores 查看当前的所有资源信号的状态，以确定当前系统是否有足够的内存分配给查询请求。该视图汇总系统中所有查询请求的授予内存，为每个资源池（Resource Pool）返回两行，一行是常规的资源信号，另一行是小查询（ small-query）的资源信号，所谓小查询的资源信号是指：申请的授予内存小于5MB，查询开销（Query Cost）小于3个开销单位（Cost Unit）。

三，查看内存书记统计的保留内存

在SQL Server中，只有内存书记（Memory Clerk）能够分配内存，任何一个需要使用内存的对象，必须创建自己的Memory Clerk，并使用该Memory clerk来分配内存。

Memory Clerk会记录已经分配内存的数量，SQL Server 使用 ‘MEMORYCLERK_SQLQERESERVATIONS’ 来分配执行排序或哈希操作所需要的内存，可以使用 sys.dm_os_memory_clerks 来查看系统中执行排序或哈希操作时分配的总内存。

select type,
    pages_kb,
    virtual_memory_reserved_kb,
    virtual_memory_committed_kb,
    shared_memory_reserved_kb,
    shared_memory_committed_kb,
    page_size_in_bytes
from sys.dm_os_memory_clerks 
where type = 'MEMORYCLERK_SQLQERESERVATIONS'
and memory_node_id<>64

在该视图中，内存节点ID（memory_node_id）为64时，只在DAC中使用，该节点不会关联到任何物理内存节点（Physical Memory Node），仅是为了支持DAC而专门设计的一个逻辑内存节点（Logical Memory Node）。

四，数据溢出到tempdb

如果授予内存不足，对查询请求会有什么副作用？授予内存不足，会导致请求数据溢出到tempdb，实际上，是溢出到硬盘，这种警告，是查询语句的性能低下的一个信号。

在执行一个查询语句时，发现 TOP(10) 和 TOP(100)所用时间差距很大。在对其调优时，发现排序操作符（Sort Operator）消耗的时间高达95%，并抛出警告：

Operator used tempdb to spill data during execution with spill level 1

SQL Server 之所以抛出警告，是因为排序操作符的实际值（Actual Number of Rows）明显大于评估值（Estimated Number of Rows），SQL Server引擎根据执行计划的评估值，计算授予内存的数量，然后分配授予内存。在SQL Server 真正进行排序操作时，由于查询请求预先被分配的授予内存少于实际需要的内存，这导致SQL Server必须把中间结果集转存到tempdb中。这种情况虽然不会导致错误，但是会降低查询的性能。如果能使全部数据都在内存中排序，那么就能提高查询语句的性能。

SQL Server引擎计算授予的评估值是根据系统维护的统计信息（statistics）来评估的，如果索引的统计信息（Index Statistics）过期，或者长时间未刷新，这会导致查询优化器（Query Optimizer）低估实际值（Actual number of rows），导致Actual Number of Rows明显大于Estimated Number of Rows，因此，必须刷新索引的统计信息（Statistics），使查询优化器基于正确的统计信息做评估。还可以优化查询语句，使其能够引用索引；或者增加必要的内存，或者创建正确的索引，或者重新编译执行计划。

参考文档：

Memory Meditation: The mysterious SQL Server memory consumer with Many Names

Understanding SQL server memory grant

Identifying and Solving Sort Warnings Problems in SQL Server

Never Ignore a Sort Warning in SQL Server

SQL Server 2012: Sort operator causing tempdb spill

Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics

SpillToTempDb warning and SpillLevel’s mapping to single versus multiple pass

作者：悦光阴

出处： http://www.cnblogs.com/ljhdo/

本文版权归作者和博客园所有，欢迎转载，但未经作者同意，必须保留此段声明，且在文章页面醒目位置显示原文连接，否则保留追究法律责任的权利。

分类: 数据库管理