sql服务器内存不足

of this series, we started with most important SQL Server performance memory metrics. We described ，我们从最重要SQL Server性能内存指标开始。 我们描述了 Pages/sec and Pages / sec和 Page faults/sec, what they indicated, and what the normal values were In this part, we will present three more memory metrics that affect SQL Server performance – Page Faults / sec的含义，指示的含义以及正常值。在这一部分中，我们将介绍另外三个影响SQL Server性能的内存指标– Available memory bytes, Total server memory, and Target server memory 可用内存字节，总服务器内存和目标服务器内存

可用字节 (Available bytes)

“The Available Bytes counter indicates how many bytes of memory are currently available for use by processes.” [1]

“ Available Bytes计数器指示当前有多少字节的内存可供进程使用。” [1]

SQL Server doesn’t use a fixed amount of memory by default. The memory used is determined dynamically, based on SQL Server current needs and available system memory. If SQL Server needs more memory than currently allocated and there’s memory available in the system, the operating system will allow additional memory allocation for SQL Server. If on the other hand, SQL Server allocated more memory that it needs at the moment, the excessive memory will be released to the operating system and the operating system can allocate it to other applications

默认情况下，SQL Server不使用固定数量的内存。 根据SQL Server当前的需求和可用的系统内存，动态确定使用的内存。 如果SQL Server需要的内存比当前分配的更多，并且系统中有可用的内存，则操作系统将允许为SQL Server分配更多的内存。 另一方面，如果SQL Server分配了当前所需的更多内存，过多的内存将释放给操作系统，并且操作系统可以将其分配给其他应用程序

If the memory Available bytes value is constantly lower than 100 MB, it indicates that there is insufficient memory on the server, or that there is an application that is not releasing memory. In case of insufficient memory, the Pages/sec and Page faults/sec values will also be high, as there is not enough memory to store all needed pages, and intensive writing into and reading from memory occurs

如果“内存可用字节”值始终小于100 MB，则表明服务器上的内存不足，或者有某个应用程序没有释放内存。 在内存不足的情况下，由于没有足够的内存来存储所有需要的页面，并且页面会频繁地写入和读取内存，因此页面/秒和页面错误/秒的值也会很高。

When there’s enough memory on the server, the available memory is high, page faults are rare, and Pages/sec and Page faults/sec are low

当服务器上有足够的内存时，可用内存就很高，分页错误很少，而Pages / sec和Page Faults / sec则很低

For good SQL Server performance, available memory should not be less than 200 MB. The threshold value depends on the system configuration and usage. It’s recommended to have a dedicated machine for SQL Server where no other applications run, so the physical memory is used only by the operating system and SQL Server

为了获得良好SQL Server性能，可用内存不应少于200 MB。 该阈值取决于系统配置和使用情况。 建议为没有其他应用程序运行的专用SQL Server计算机，因此物理内存仅由操作系统和SQL Server使用

If there are peaks in available memory, check the disk activity. If disk activity increases when available memory is low, check whether it’s due to complex SQL Server tasks – backups, restores, etc. If not, this indicates memory pressure on the system

如果可用内存中有峰值，请检查磁盘活动。 如果可用内存不足时磁盘活动增加，请检查是否是由于复杂SQL Server任务-备份，还原等引起的。如果不是，则表明系统内存不足

If available memory is constantly low and server load cannot be reduced, it’s necessary to add more RAM

如果可用内存一直很低并且无法减少服务器负载，则必须添加更多RAM

服务器总内存（KB） (Total Server Memory (KB))

“Specifies the amount of memory the server has committed using the memory manager.” [2]

“指定服务器使用内存管理器已提交的内存量。” [2]

In other words, this is the amount of memory currently assigned to SQL Server

换句话说，这是当前分配给SQL Server的内存量

The Total Server Memory value is a metric tracked by SQL Server performance counter. It can be obtained by querying the sys.dm_os_performance_counters system view

服务器总内存值是SQL Server性能计数器跟踪的一个指标。 可以通过查询sys.dm_os_performance_counters系统视图获得

 SELECT object_name ,counter_name, cntr_valueFROM sys.dm_os_performance_countersWHERE counter_name = 'Total Server Memory (KB)' 

If the Total Server Memory (KB) value is consistently high, it means that SQL Server is constantly using a lot of memory and that the server is under memory pressure

如果“ 服务器总内存”（KB）值始终很高，则表明SQL Server一直在使用大量内存，并且服务器承受内存压力

目标服务器内存（KB） (Target Server Memory (KB))

“Indicates the ideal amount of memory the server can consume.”[2]

“表明服务器可以消耗的理想内存量。” [2]

The Target Server Memory (KB) value shows how much memory SQL Server needs to for best performance

目标服务器内存（KB）值显示SQL Server为获得最佳性能所需的内存量

Similar to Total Server Memory, the Target Server Memory (KB) value is tracked by SQL Server counters and can be obtained from the sys.dm_os_performance_counters system view

与总服务器内存类似，目标服务器内存（KB）值由SQL Server计数器跟踪，可以从sys.dm_os_performance_counters系统视图中获取。

 SELECT object_name ,counter_name, cntr_valueFROM sys.dm_os_performance_countersWHERE counter_name = 'Target Server Memory (KB)' 

When the Total Server Memory and Target Server Memory values are close, there’s no memory pressure on the server

关闭“ 总服务器内存”和“ 目标服务器内存”值时，服务器上没有内存压力

In other words, the Total Server Memory/ Target Server Memory ratio should be close to 1. If the Total Server Memory value is significantly lower than the Target Server Memory value during normal SQL Server operation, it can mean that there’s memory pressure on the server so SQL Server cannot get as much memory as needed, or that the Maximum server memory option is set too low

换句话说，“ 总服务器内存” /“目标服务器内存”之比应接近1。如果在正常SQL Server操作期间“ 总服务器内存”值明显低于“ 目标服务器内存”值，则可能意味着服务器上存在内存压力因此，SQL Server无法获得所需的内存，或者“ 最大服务器内存”选项设置得太低

As shown, the Available memory bytes metrics is one of the SQL Server performance parameters that can be used to indicate performance problems without setting a baseline first. If the available memory is constantly low, the performance is suffering. The Total Server Memory (KB) and Target Server Memory (KB) values should be analyzed together, as their ratio helps determining the right course of action to fix performance issues

如图所示，“ 可用内存字节”度量标准是SQL Server性能参数之一，可用于指示性能问题而无需先设置基准。 如果可用内存一直很低，则性能会受到影响。 应该将总服务器内存（KB）和目标服务器内存（KB）值一起分析，因为它们的比率有助于确定解决性能问题的正确方法。

翻译自:

sql服务器内存不足