理解内存----SQL Server内存

23ee

EMORYCLERK_SQLQUERYPLAN），这使得故障排除更加容易，因为你可以看到每个Clerk所做的内存分配，看看谁拥有什么。您可以使用DMV sys.dm_os_memory_clerks DMV来查看所有内存Clerk的详细信息。例如，在生产工作负载上运行SQL Server 2012企业版的实例下面，查询产生如下图所示的结果：

• SELECT [type],memory_node_id,pages_kb,virtual_memory_reserved_kb,  
• virtual_memory_committed_kb,awe_allocated_kb  
• FROM sys.dm_os_memory_clerks  
• ORDER BY virtual_memory_reserved_kb DESC;  
  

缓存
SQL Server使用三种类型的缓存机制：对象存储，缓存存储和用户存储。对象存储用于缓存同种类型的无状态的数据，但缓存缓存和用户存储是最常见的。两者很相似，因为都是缓存。它们的主要区别是，用户存储必须是由使用开发框架的自身存储语义来创建，而缓存存储则实现对前面提到的用于提供更小内存分配粒度的存储对象的支持。从本质上讲，用户存储主要用于微软内部不同的开发团队实现SQL Server功能的特定缓存，所以你可以以相同的方式看待缓存存储和用户存储。要查看SQL Server上实施的不同的缓存，可以使用DMVsys.dm_os_memory_cache_counters。例如，运行下面的查询会显示所有可用的缓存，它们以空间消费的总量排序：

• SELECT [name],[type],pages_kb,entries_count  
• FROM sys.dm_os_memory_cache_counters  
• ORDER BY pages_kb DESC;  
  

特定的缓存如下：

• CACHESTORE_OBJCP -----为对象（如存储过程，函数和触发器）编译计划
• CACHESTORE_SQLCP -----为不在存储过程中的SQL语句和批处理缓存计划
• CACHESTORE_PHDR -----用于视图、约束和默认值的Algebrizer树，Algebrizer树是解析表名和列名的SQL文本。
  

缓冲池
缓冲池包含并管理SQL Server的数据缓存。其内容信息可通过DMV sys.dm_os_buffer_descriptors DMV来查看。例如，下面的查询将返回每个数据库的缓存使用量，以MB为单位：

• SELECT count(*)*8/1024 AS 'Cached Size (MB)'  
• ,CASE database_id WHEN 32767 THEN 'ResourceDb'  
• ELSE db_name(database_id) END AS 'Database'  
• FROM sys.dm_os_buffer_descriptors  
• GROUP BY db_name(database_id),database_id  
• ORDER BY 'Cached Size (MB)' DESC  
  

监视SQL Server的缓冲池，是查看内存压力的重要方式，性能监视器提供了大量的计数器来帮助你做到这一点，以便快速的洞察，包括以下计数器：
MSSQL$<instance >:Memory Manager\Total Server Memory (KB)-------表示缓冲池的当前大小
MSSQL$<instance >:Memory Manager\Target Server Memory (KB)-------表示缓冲池的理想大小。运行一段时间没有内存压力的服务器上，总量和目标应该是几乎相同的。如果总量明显低于目标，要么是工作负载不能满足SQL Server的增长，要么是SQL Server因为内存压力不能增大缓冲池，在这种情况下，你需要进一步调查。
MSSQL$<instance >:Buffer Manager\Page Life Expectancy---------表示SQL Server希望一个页面装载进缓冲池后停留在缓存中的时间量（秒）。在内存的压力下，数据页被频繁地从缓存里刷新出去。微软建议最少300秒，小于300秒是糟糕的情况。在使用大量物理内存的系统中，这将很容易达到几千秒。
计划缓存
创建执行计划是好事且资源密集的，因此，如果SQL Server能找到执行一段代码的好方式，将是一件有意义的事，应该尝试为后续的请求复用它。计划缓存用于缓存所有的执行计划，以备复用。你可以使用DMV sys.dm_exec_cached_plans来查看计划缓存中的内容和确定它当前的大小，或执行 DBCC MEMORYSTATUS并找到“Procedure Cache”，这里你讲会看到缓存中计划的数量和缓存大小，它们在8KB的页面里。DBCCMEMORYSTATUS提供了大量有关SQL Server内存状态的有用信息，但你会发现，DMV则提供了更加灵活的输出，因此要尽可能习惯利用DMV找到相同的信息。下面的DMV是一个良好的开端：

• sys.dm_os_memory_nodes
• sys.dm_os_memory_clerks
• sys.dm_os_memory_objects
• sys.dm_os_memory_cache_counters
• sys.dm_os_memory_pools
  

下面的查询可以得到缓存的计划数和大小：

• SELECT count(*) AS 'Number of Plans',  
• sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)'  
• FROM sys.dm_exec_cached_plans  
  

计划缓存的最大值可以通过如下方式计算得到：

• 75%的Server内存（0~4GB+）
• 10%的Server内存（4GB~64GB+）
• 5%的Server内存（>64GB）
  

因此，有32GB RAM的系统将有一个最大计划缓存值：75%*4+10%*28=5.8（GB）
查询/工作空间内存
在SQL Server中，查询内存（也称为工作空间内存）用于临时存储在执行一个查询时哈希和排序期间的结果。查询内存是从缓冲池分配出来的，它也有自己的内存Clerk，你可以通过DMV sys.dm_exec_query_memory_grants查看其相关信息.作为查询内存的可用空间量是可以动态管理的，它占用25%~75%的缓冲池，如果缓冲池没有压力，它可以增长到更大。5%的查询内存是为小查询预留的，这些小查询需要小于5MB的内存，成本小于3。SQL Server根据所需资源量为查询指定成本。
没有单个的查询能够获得超过总量20%的查询内存，这是为了确保其它查询仍然能够被执行。除了这个保障，SQL Server还实施了一个查询内存许可队列，每个包含哈希或排序操作的查询在执行之前，都要通过这个全局队列，查询根据查询成本被组织到5个队列。每个查询基于成本被放进合适的队列中，每个队列实现了先来先服务的策略。这种方式能够使哪些需要较少内存的小查询得到处理，即使较大的查询在等待足够的可用内存。
下图显示了有1GB查询内存的服务器上，基于查询成本的五个队列组成的全局内存许可队列。在图片的底部的框中包含现有八个内存授予共920MB，剩下104MB的可用空间。第一个请求到达的是120MB，走进Q3。该请求不能被立即分配，因为只有104MB是可用的。接下来的请求只有20MB，并进入第二季度。这个请求可以立即得到满足，因为有多个队列，意味着它不会被卡在仍然在等待的第一个请求之后。

查询等待选项
如果花太多时间等待内存授予，查询可能会超时。超时持续时间由Query Wait option控制，Query Wait option可以使用sp_configure或在Management Studio里面的服务器属性的高级页面上进行修改。默认值是-1 ，这相当于该查询成本的25倍，以秒为单位。对于查询等待的任何正值，将被用作以秒为单位的超时值。对于一个包含查询的事务而言，有可能出现等待内存授予以保持锁是打开的，并在超时之前引起一个阻塞问题。在这种情况下，更低的查询等待值能够通过更快引起查询超时，来降低对其他任务的影响。然而，要首先尝试降低查询的内存需求，或者增加可用内存，来避免在改变全局选项（如Query Wait）之前引起超时，因为改变全局选项会影响服务器上所有正在运行的查询。默认设置允许动态的超时值，它根据查询成本变化，所以它通常是最好的选择。
查询内存诊断
除了前面提到的DMV，还有很对不同的方式来获取SQL Server上查询内存使用的信息。性能监视器（Performance Monitor）提供了如下计数器，这些计数器都可以在实例的Memory Manager中找到：

• Granted Workspace Memory (KB)--------当前在用的查询内存的总量
• Maximum Workspace Memory (KB)--------SQL Server为查询内存标记的内存总量
• Memory Grants Pending---------队列中正在等待的内存授予数
• Memory Grants Outstanding---------当前在用的内存授予数
  

RESOURCE_SEMAPHORE等待类型是内存授予上的一个等待，如果你发现它在sys.dm_os_wait_stats的结果集中靠近顶部，那么就说明你的系统正在为足够快地进行内存授予而挣扎。在等待一个内存授予时，你也会遇到性能问题，不仅仅是一个查询超时。在一个执行计划中或当你分析一个SQL跟踪时，如果你选择了相关的事件，你或许会注意到哈希警告或排序警告消息。这些发生在内存授予不能满足查询的要求时。当哈希建立不适合在内存，必须溢出到磁盘（其实际写入tempdb中）时，会发生哈希警告。当必需一个多通道（multi-pass）时，会发生排序警告，因为授予的内存不足。这两个警告通常会发生，因为SQL Server查询优化器做出了错误的选择，这个错误选择是因为不准确的统计数据或缺少有用的统计数据。有关这方面的详细信息，在后面的查询处理和执行章节中会介绍。