理解SQL Server内存授权

生活如麻

　　理解SQL Server内存授权
　　翻译自：https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/
　　这篇文章描述了在Microsoft SQL Server里查询内存授权如何工作。它适用于SQL Server 2005和2008。由Microsoft SQL Server引擎团队的Jay Choe所写。
　　——————————————————————————
　　查询内存授权（也叫查询工作缓存），是服务器内存的一部分，用于当排序和行连接时存储临时行数据。它被称为“授权”是因为服务器需要那些查询在实际使用内存前预留下来。这个预留提高了在服务器负载下的查询可靠性，因为一个带有预留内存的查询，在运行时不可能命中内存溢出，并且服务器阻止一个查询占用整个服务器内存。在以下方面，我会描述SQL Server如何预估查询内存需求，并且当多个查询完成时它如何控制内存授权。
　　当SQL Server收到一个用户查询，它遵循定义好的步骤来为用户生成一个结果。首先，它创建一个编译的计划，它是像如何连接行的逻辑指令的集合。接下来，基于编译的计划创建一个执行计划。这个执行计划包含指令将所有逻辑推理翻译为实际对象，和用于查询执行的跟踪架构。最后，服务器从指令树的顶部开始执行。创建一个编译的计划是昂贵的，因为服务器需要从上百的潜在候选对象中找出最优计划。区别编译和执行对整体的服务器性能有帮助，因为每个编译的计划可以被缓存，并在多个执行计划中共享。这个内存授权预估遵从了相同的整体顺序。它在编译计划中保存参数，并有一个在执行时计算实际授权大小的机制。
　　内存消费者
　　既然我简单说明了查询执行的生存期，我想显示在整个服务器内存使用里查询内存授权适用于哪里。正如前一段所示，一个成功的查询执行包含3个主要内存消费者：编译（compile）、缓存（cache）和内存授权（memory grant）。

• 　　编译（查询优化）：从上百个候选者中构建和搜索最佳计划显然需要足够的内存。这个使用的生存期显然很短，因为只要最佳计划被找到，优化器就释放内存。缺少足够的内存将会导致在编译中的延时，和可能无效率的（慢的）计划。
  
• 　　缓存：好到最佳计划在CPU和内存使用上是代价高的。SQL Server尝试存储编译的计划到缓存中用于后续重用。这个内存使用的生存期是长期的。缺少缓存内存将会导致更没有必要的重编译。
  
• 　　内存授权：这个内存用于为排序和哈希连接存储临时行。内存授权的生存期与查询的生存期一样。缺少可用内存授权会导致一个查询使用硬盘，将影响查询性能。
  

　　SQL Server在这3个消费者之间使用叫做“memory broker”的内部设施来维护平衡。基于使用和物理内存的可用，memory broker设置使用限制，并告诉每个组件如果它预测是短期使用，就收缩内存。
　　授权参数
　　当SQL Server创建了一个编译的计划，它计算两个内存授权参数叫做“required memory”和“additional memmory”。

• 　　Required memory：运行排序和哈希连接需要的最小内存。它被称为需要的，是因为如果一个查询没有这个可用内存将不会开始。SQL Server使用这个内存去创建内部数据结构来处理排序和哈希连接。
  
• 　　Additional memory：需要在内存中存储所有临时行的数量。它依赖于预估的基数（预计的行数和行大小）。它被称为额外的，是因为一个查询在缺少这个内存时通过在磁盘上存储部分临时行来幸存下来。如果总数超过预设限制，一个查询将不能保证获得全部数量。
  

　　例如，让我们考虑一个简单的查询，它需要在每10个字节排序1百万行。这个查询需要的内存是512KB，因为这是SQL Server需要用于构造内部数据结果用于处理一个排序的最小数量。因为它要花10MB来存储所有行，额外的内存将会是10MB（当间接费用包含在内会略高）。如果编译的计划有多个排序和连接，这个计算就变得很复杂，因为SQL Server也考虑了对于更多有效内存使用的每个操作的生存期。你通常会看到比所有排序和连接的总和更小的预估。如果你对在操作之间的相对内存使用感兴趣，你可以检查在Showplan XML里的标签。以下部分显示了当在运行时计算授权大小时这些参数如何使用。
　　DOP依赖
　　如果SQL Server有多于1个CPU，它为了提高性能在并行模式通过在并行workers之间分享工作来运行一个查询。这些workers彼此之间独立，并使用“并行操作（也叫交换exchange）”来传输处理过的行。这个并行模式增加了内存使用，因为每个worker需要排序或哈希连接的它自己的拷贝，并且并行操作对于传输的行的临时存储需要缓存。因此DOP N将会使用N个并行workers，这个查询将需要Required memory的N倍多。另一方面，处理的行总数（和存储它们的内存消耗）将不会使用DOP而改变。这就意味着Additional memory无论DOP设置如何都会保持一样。从SQL Server 2008开始，并行操作的缓存内存使用也作为内存授权需要的一部分。
　　内存授权进程
　　在前面部分，我们讨论了并行如何影响了查询内存需求。在这部分，我们将讨论SQL Server如何占用服务器内存和考虑大量的并发查询。服务器需要考虑这样的动态因素来避免提交内存超过了物理限制。它在2个不同的步骤里完成。首先，服务器对于给定的查询计算需要授权多少内存。然后它使用内部设备叫做Resource Semaphore来预留实际的内存，或者如果有太多查询请求内存，它会控制内存。首先以下步骤限制了如何确定请求大小。

• 　　服务器基于计划和服务器状态来决定并行（DOP）。
  
• 　　服务器检查内存授权是否需要。如果不需要，服务器可以立即开始查询。例如，一个简单的没有“ORDER BY”或“GROUP BY”的查询序列可能不需要内存授权。
  
• 　　对于每个查询服务器计算内存限制。默认，它是总查询内存（它被memory broker设置为大约总服务器内存的90%）的25%（在32位SQL Server 2005里是20%）。这个对每个查询的限制帮助组织一个查询占用整个服务器内存。这个百分比是在SQL Server 2008里可配置的。
  
• 　　服务器计算理想的查询内存为 Required*DOP + Additional ( + exchange 在SQL Server 2008里)。这是一个查询基于它的基数预估会有的数量。
  
• 　　服务器检查是否理想的内存超出了每个查询的限制。如果是的，那么服务器减少Additional memory直到总数满足在限制内。这个修正的大小叫做Requested memory。服务器让Resource Semaphore去授权Requested memory。
  

　　资源信号量
　　资源信号量（Resource Semaphore）负责当保持整个内存授权使用在服务器限制内时满足内存授权需求。

• 　　资源信号量只有当有足够的空闲可用内存时，允许一个查询预留内存。否则，一个请求的查询被强制在队列中等待。
  
• 　　当资源信号量收到一个新的请求，它首先检查是否有请求在等待。如果它找到一个，为了公平它将新的查询放到队列里，因为这个等待队列设计为先到先服务的原则。
  
• 　　资源信号量在它的队列中检查等待的查询。如果知道一个，为了公平它将内的查询放到一个等待队列，因为这个等待队列设计为先到先服务的原则，使用小的权重满足小的查询。
  
• 　　如果资源信号量没有找到一个等待的查询，或者当存在的查询返回内存，它做出了一个授权尝试。
  
• 　　当没有等待的查询，或者当一个查询返回内存，资源信号量尝试去授予内存。
  
• 　　当没有等待的查询，或者当一个存在的查询返回预留的内存时，做出了授权尝试。
  
• 　　如果它找到一个等待的查询，为了公平它将当前的查询放到一个等待队列。
  
• 　　如果没有找到任何等待的查询，它然后检查可用空闲内存。
  
• 　　如果它找到足够的空闲内存，那么Requested memory被授权，并且查询可以开始运行。
  
• 　　如果它没有找到足够的空闲内存，那么它将当前的查询放到等待队列里。
  
• 　　当足够的空闲内存变得可用时，查询信号量唤醒在等待队列里的查询。
  

　　内存授权相关问题故障排除
　　SQL Server提供了一些动态管理视图（DMV）用于帮助调查内存授权相关问题。关于以下讨论的DMV的更多详情，请参考联机帮助文档。
　　sys.dm_exec_query_resource_semaphores
　　这个DMV显示了在之前部分提到的资源信号量的当前状态。在SQL Server 2005，你可以找到它们中的2个。一个非空的max_target_memory_kb列被称为常规资源信号量（Regular Resource Semaphore），另一个称为小资源信号量（Small Resource Semaphore）。正如名字所暗示的，常规资源信号量被一般条件下的所有查询所使用，而小资源信号量当它们得等待时（查看之前部分的第6步）被小尺寸查询（小于5MB）所使用。这会提高小尺寸查询的响应时间，它被期望很快完成。注意，max_target_memory_kb列显示了在之前部分的第2步里使用的服务器内存限制。
　　sys.dm_exec_query_memory_grants
　　这个DMV显示了所有消耗内存授权的查询，包括那些在资源信号量队列等待的查询。等待的查询grant_time列为null值。资源信号量使用内部查询消耗预估来决定内存授权优先级，并且is_next_candidate列显示了当内存可用时哪个查询唤醒了。
　　sys.dm_os_wait_stats
　　这个DMV显示了所有服务器对象的等待统计信息。内存授权使用“RESOURCE_SEMAPHORE”等待类型。如果在这个等待类型你看到有标志性的等待，你可能有一个大尺寸查询的问题。
　　示例查询
　　以下示例查询显示了DMV正在使用多少内存授权。
　　找出所有在内存队列里的查询等待：
SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null　　找出谁在使用大多数查询内存授权：
SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan　　
FROM sys.dm_exec_query_memory_grants AS mg
　　
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
　　
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
　　
ORDER BY 1 DESC OPTION (MAXDOP 1)
　　使用内存授权的查询的搜索缓存：
SELECT t.text, cp.objtype,qp.query_plan　　
FROM sys.dm_exec_cached_plans AS cp
　　
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
　　
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
　　
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
　　
WHERE qp.query_plan.exist(‘declare namespace n=”http://schemas.microsoft.com/sqlserver/2004/07/showplan“; //n:MemoryFractions’) = 1