Mistake: letting SQL Server use all available RAM because it appears to be the only application on the server. The buffer pool can consume memory needed by the operating system, SSIS, SSAS, agents, or other instances. Lock Pages In Memory makes the risk greater because the working set will not be trimmed. section 10.3
Set max server memory to limit the SQL Server buffer pool, while reserving enough RAM for other processes. A starting calculation is: Max Memory = ((Server Memory − Memory Required by Other Processes) / 100) × 75. Adjust the allocation when multiple instances or additional SQL Server features share the host. section 10.14 section 10.3
A 64 GB server
On a server with 64 GB of RAM running one SQL Server instance, 48 GB may be an appropriate maximum when no other application requires significant memory. The value is specified in megabytes. section 10.3
Configure the setting with sp_configure: sp_configure 'show advanced options', 1; RECONFIGURE; GO; sp_configure 'max server memory', 49152; RECONFIGURE; GO. Verify the needs of the operating system and mandatory tools before choosing the number. section 10.3
