A common SQL Server configuration error is allowing SQL Server to use all available memory.
Te default “Maximum server memory (in MB)” setting (highlighted) allows SQL Server use ALL available memory. For the most part, SQL does a good job of managing the available memory, however, best practice is setting a maximum available memory.
For a Windows Server dedicated for use by SQL Server, general rule of thumb is to allocate approximately 80-90% of available server memory to SQL Server. Monitor the Windows Server memory usage.
If you are running ConfigMgr 2012 on the same server with SQL Server; I’d recommend allowing about 8GB of memory for ConfigMgr and the Windows Server OS, then allocate the remainder of memory to SQL Server by using the Maximum Server memory setting shown earlier. If possible, allocate in even 1024MB chunks.
Windows Server has 32GB total memory, and is running ConfigMgr 2012 and SQL Server. Subtract 8GB memory for the Server OS and ConfigMgr 2012. The remainder should be used for SQL.
32GB – 8GB = 26GB (SQL Server)
26GB * 1024 = 28672 (MB – enter in SQL property sheet)
Ideally, the server should not be using the server page file, as this will lead to performance degradation. Monitor Windows Server memory usage and make adjustments if needed.