七、内存优化（2）动态内存分配

一、动态内存分配

　　SQL Server 将根据需要动态获取并释放内存。虽然该选项仍然存在且在有些环境下需要用到，但通常情况下管理员不必指定为 SQL Server 分配多少内存。

　　所有数据库软件的主要设计目标之一是尽量减少磁盘 I/O，因为磁盘的读取和写入操作占用大量资源。SQL Server 在内存中生成缓冲池，以保存从数据库读取的页。SQL Server 中的大量代码专门用于尽量减少磁盘与缓冲池之间的物理读写次数。SQL Server 设法在以下两个目标之间达到平衡：

（1）防止缓冲池变得过大，从而导致整个系统内存不足。

（2）尽量增加缓冲池达的大小，以便尽量减少数据库文件的物理 I/O。

二、监视计数器以确认内存不足

　　定期监视 SQL Server 的实例以确认内存使用量在正常范围内。如果要监视内存不足的情况，请使用下列对象计数器：

（1）Memory: Available Bytes

　　此计数器指示当前有多少内存（以字节为单位）可供进程使用。如果它的值低，则表示计算机总内存不足或应用程序没有释放内存。

（2）Memory: Pages/sec

　　此计数器指示由于页错误而从磁盘取回的页数，或由于页错误而写入磁盘以释放工作集空间的页数。如果它的比率高，则表示分页过多，需要同时监视 Memory: Page Faults/sec 计数器以确保磁盘活动不是由分页导致。

　　分页率偏低（以及由此产生的页错误）是正常的，即使计算机有大量的可用内存。Windows 虚拟内存管理器 (VMM) 在剪裁 SQL Server 和其它进程的工作集大小时会收走这些进程的页。此 VMM 活动会导致页错误。若要确定分页过多是由 SQL Server 还是由其它进程导致，请监视用于 SQL Server 进程实例的 Process: Page Faults/sec。

三、服务器内存选项

1. 内存选项

　　默认情况下，SQL Server 将根据可用系统资源动态改变其内存要求。如果 SQL Server 需要更多内存，它会查询操作系统以确定是否有可用的空闲物理内存，然后使用可用内存。如果 SQL Server 当前不需要分配给它的内存，它会将内存释放给操作系统。

　　也可以覆盖此选项通过 min server memory 和 max server memory 服务器配置选项来动态使用内存。

2. 最小服务器内存（min server memory）

　　使用 min server memory 确保 SQL Server 实例的缓冲池可用的最小内存量。SQL Server 在启动时将不立即分配在 min server memory 中指定的内存量。当内存使用量达到该值后，SQL Server 不能从已分配的缓冲池中释放内存，除非降低 min server memory 的值。

　　min server memory 的默认设置是 0。

3. 最大服务器内存（max server memory）

　　默认情况下，SQL Server 的内存要求会根据可用系统资源的情况动态地变化。配置 max server memory 选项可另外留出一些可用内存，以满足Windows和其它应用程序的需要。当内存使用量达到 max server memory 中指定的值，SQL Server 无法超过此内存使用量，除非增加 max server memory 的值。若要减小 max server memory，可能需要重新启动 SQL Server 以释放内存。

　　max server memory 的默认设置是 2147483647，最小可设置为16MB。

　　注意：如果将 max server memory 设置为最小值，则可能会严重降低 SQL Server 的性能，甚至使其无法启动。如果在更改此选项之后无法启动 SQL Server，则请使用 –f 启动选项进行启动，并将 max server memory 重置为以前的值。

4. 建议

　　建议允许 SQL Server 动态使用内存；但可以手动设置内存选项并限制 SQL Server 可以访问的内存量。在设置 SQL Server 的内存量之前，请通过从总物理内存中减去操作系统和任何其他 SQL Server 实例所需的内存（如果计算机并非完全由 SQL Server 专用，则还要减去其他系统使用的内存量）来确定适当的内存设置。这个差值就是可以分配给 SQL Server 使用的最大内存量。

　　在减小 max server memory 值之前，请在加载时使用性能监视器来检查 SQLServer:Buffer Manager 性能对象，并注意 Stolen pages 和 Reserved pages 计数器的当前值。这些计数器将内存报告为 8K 页的数目。应将 max server memory 设置为大于这两个值的和，以免出现内存不足错误。对于合理的最低 max server memory 设置（以 MB 为单位），近似值是 ([Stolen pages] + [Reserved pages])/ 100。

5. 多实例时的内存配置

　　当运行多个数据库引擎实例时，可以使用三种方法来管理内存：

（1）使用 max server memory 控制内存使用量

　　为每个实例建立最大设置，注意总的允许设置值不能大于计算机上的物理总内存。可能需要为每个实例提供与预期的工作负荷或数据库大小成正比的内存。这种方法的优势体现在：当启动新的进程或实例时，可以立即为这些进程或实例提供可用内存。这种方法的缺点为：如果没有运行所有实例，则所有运行中的实例都无法使用剩余的可用内存。

（2）使用 min server memory 控制内存使用量

　　为每个实例建立最小设置，以使这些最小值的和比计算机上总的物理内存小 1-2 GB。此外，可能需要建立与该实例的预期负荷成正比的最小值。这种方法的优势体现在：如果没有同时运行所有实例，则运行中的实例可以使用剩余的可用内存。当计算机上存在其他占用大量内存的进程时，这种方法也十分有用，因为它可确保 SQL Server 至少获得合理的内存量。这种方法的缺点为：当启动新的进程（或任何其他进程）时，它可能会花费一些时间运行实例以释放内存，如果实例必须将修改后的页写回到数据库中来释放内存，则花费的时间可能会更长。还可能需要显著增大分页文件的大小。

（3）不执行任何操作（不推荐）

　　带有工作负荷的第一个实例通常分配所有的内存。稍后启动的空闲实例最终可能会只使用最少的可用内存量运行。SQL Server 不会尝试均衡分配各个实例的内存使用量。但是，所有实例都将响应 Windows 内存通知信号以调整其缓冲池的大小。Windows 不会使用内存通知 API 来平衡各个应用程序使用的内存。它只提供有关系统内存可用性的全局反馈。

　　您可以在不重新启动实例的情况下更改这些设置，以便可以轻松地进行尝试以找到适用于您使用模式的最佳设置。

本文结语：

　　默认由数据库引擎动态分配内存，也可以通过服务器内存选项配置最小和最大内存。