SQL Server内存使用的简单介绍
场景：
SQL Server为什么内存占用越来越多？
怎么设置SQL Server的最大内存？怎么限制数据库的内存占用？
SQL Server配置了最大内存占用，为什么实际占用超过了配置的值？
SQL Server内存占用应该设置多大才合理？
等等其他能从理论中发现的隐藏flag...
1 SQL Server 内存体系结构
防止缓冲池变得过大，从而导致整个系统内存不足。
尽量增加缓冲池达的大小，以便尽量减少数据库文件的物理 I/O。
2 动态内存管理
-- sql 查询当前分配内存 -- SELECT physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB, virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, virtual_address_space_available_kb/1024 AS sql_VAS_available_MB, page_fault_count AS sql_page_fault_count, memory_utilization_percentage AS sql_memory_utilization_percentage, process_physical_memory_low AS sql_process_physical_memory_low, process_virtual_memory_low AS sql_process_virtual_memory_low FROM sys.dm_os_process_memory;
随着在运行 SQL Server 实例的计算机上启动其他应用程序，这些应用程序将会占用内存，从而使可用物理内存量降到 SQL Server 目标以下。 SQL Server 实例将调整其内存使用量。 如果另一个应用程序已停止，并且可用内存增多，SQL Server 的实例会增加其内存分配的大小。 SQL Server 每秒可以释放和获取几 MB 的内存，从而根据内存分配变化快速做出调整。
3 服务器内存配置选项
将“最大服务器内存(MB)”值设置得太高可能导致一个 SQL Server 实例与同一主机上承载的其他 SQL Server 实例争用内存。
但是，将“最大服务器内存(MB)”设置得太低会降低性能，可能会导致 SQL Server 实例中出现内存压力和性能问题。
将“最大服务器内存(MB)”设置为最小值甚至可能导致无法启动 SQL Server。 如果在更改此选项后无法启动 SQL Server，请使用 -f 启动选项来启动它，并将“最大服务器内存(MB)”重置为其之前的值。
建议不要将“最大服务器内存(MB)”和“最小服务器内存(MB)”设置为相同值或接近相同的值。
SQL Server 不会在启动时立即分配在“最小服务器内存(MB)”中指定的内存量。 不过，除非调低“最小服务器内存(MB)”的值，否则当内存使用量由于客户端负载而达到该值后，SQL Server 不能释放内存。 例如，在同一台服务器上同时安装多个 SQL Server 实例时，请考虑设置“最小服务器内存(MB)”参数，使其为实例预留内存。
为了确保来自基础主机的内存压力不会尝试从来宾虚拟机 (VM) 上的缓冲池释放超过可接受性能所需的内存，在虚拟环境中设置“最小服务器内存(MB)”值非常有必要。 理想情况下，虚拟机中的 SQL Server 实例不必与虚拟主机主动内存解除分配进程竞争。
SQL Server 并不一定分配“最小服务器内存(MB)”中指定的内存量。 如果服务器上的负载从不需要分配“最小服务器内存(MB)”中指定的内存量，则 SQL Server 将使用更少的内存。
3.2 最大服务器内存
在设置“最大服务器内存(MB)”配置之前，在正常操作期间监视托管 SQL Server 实例的服务器的总体内存消耗，以确定内存可用性和要求。 对于初始配置，或者当没有机会收集一段时间内的 SQL Server 进程内存使用情况时，请使用以下通用最佳做法方法，为单个实例配置最大服务器内存 (MB)：
从总 OS 内存中减去“最大服务器内存(MB)”控制之外的潜在 SQL Server 线程内存分配量的同等值，这个量是堆栈大小1乘以计算出的最大工作线程数2。
然后减去在“最大服务器内存 (MB)”控制范围外的其他内存分配的 25%，例如备份缓冲区、扩展存储过程 DLL、使用自动化过程（sp_OA 调用）创建的对象以及来自链接服务器提供程序的分配。 这是一个一般近似值，实际情况可能会有所不同。
对于单个实例设置，剩下的应该就是“最大服务器内存(MB)”设置。
3.3 手动设置选项
--使用 Transact-SQL，设置最小和最大服务器内存-- sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'max server memory', 12288; GO RECONFIGURE; GO --查询返回当前配置的值和当前使用的值的相关信 SELECT [name], [value], [value_in_use] FROM sys.configurations WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';
在对象资源管理器中，右键单击服务器并选择 “属性” 。
选择“服务器属性”窗口的“内存”页。 这会显示最小服务器内存和最大服务器内存的当前值。
在“服务器内存”选项中，输入最小服务器内存和最大服务器内存所需的数字。
3.4 “锁定内存页”
将 LPIM 与未考虑系统中其他内存消耗者的错误配置的最大服务器内存 (MB) 设置一起使用可能会导致不稳定，具体取决于其他进程所需的内存量，或超出最大服务器内存 (MB) 范围的 SQL Server 内存要求。
在“开始”菜单上，选择“运行”。 在“打开” 框中，键入 gpedit.msc 将打开 “组策略” 对话框。
在“本地组策略”控制台中，展开“计算机配置”。
展开 Windows 设置。
展开“安全设置”。
展开“本地策略”。
选择 “用户权利指派” 文件夹。 细节窗格中随即显示出策略。
在窗格中，滚动到“锁定内存页”策略并双击它。
在“本地安全策略设置”对话框中，选择“添加用户或组...”。 添加 SQL Server 服务帐户。 若要确定 SQL Server 实例的服务帐户，请参阅 SQL Server 配置管理器或在 sys.dm_server_services 中查询 service_account。
重启实例，使此设置生效。
-- 查看“锁定内存页”状态 SELECT sql_memory_model_desc FROM sys.dm_os_sys_info; --sql_memory_model_desc 的以下值指示 LPIM 的状态： ------CONVENTIONAL。 未授予“锁定内存页”权限。 ------LOCK_PAGES。 已授予“锁定内存页”权限。 ------LARGE_PAGES。 已在启用了跟踪标志 834 的企业模式中授予“锁定内存页”权限。 这是一种高级配置，不建议对大多数环境使用。 --使用以下方法确定 SQL Server 实例是否正在使用锁定页 ----检查locked_page_allocations_kb 的非零值 SELECT osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb FROM sys.dm_os_memory_nodes omn INNER JOIN sys.dm_os_nodes osn ON (omn.memory_node_id = osn.memory_node_id) WHERE osn.node_state_desc <> 'ONLINE DAC';
4 常见疑问
问题1：设置了最大内存占用，但是为什么实际内存占用略大于设置值。
大型列存储索引查询
大型行存储上的批处理模式
会使用大量内存来执行哈希和排序操作的列存储索引（重新）生成
需要较大内存缓冲区的备份操作
需要存储较大输入参数的跟踪操作
② SQL Server 进程将获取超过“最大服务器内存 (MB)”选项指定值的内存。 内部和外部组件都可以分配缓冲池以外的内存，这将占用额外的内存，但是分配给缓冲池的内存通常仍在 SQL Server 占用的内存中占最大份额。