Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 71

Each of these options is covered in the following sections.

Looking at the SQL Server's Maximum Usage
With this method, you set SQL Server to dynamically manage memory and then monitor the
MSSQL$<instance>:Memory Manager\Total Server Memory (KB) counter using Performance
Monitor. This counter measures SQL Server's total buffer pool usage.

The Total Server Memory value will decrease if other requirements outside SQL Server need more
physical memory than is currently free, and then increase again to use any free memory. If you
monitor this counter for a period of time that is representative for your business (i.e., it includes
busy and slack periods), you can then set Max Server Memory to the lowest value that was observed
for Total Server Memory (KB), and you won't have to worry about SQL Server having to shrink its
usage during normal operations.

Determining the Maximum Potential for Requirements Outside SQL Server
This option is the most popular, as the aim is to calculate the worst-case scenario for memory
requirements other than SQL Server's. You should allow for the following:
➤

2GB for Windows

➤

xGB for SQL Server worker threads. You can i nd your max workers count by querying
sys.dm_os_sys_info . Each thread will use 0.5MB on x86, and 2MB on x64.

➤

512MB, if you use linked servers, extended stored procedure dlls, or objects created using
Automation procedures (sp_OA calls)

➤

1-3GB, for other applications that might be running on the system, such as backup
programs or anti-virus software

For example, on a server with eight CPU cores and 64GB of RAM running SQL Server 2012, a
third-party backup utility, and virus checker, you would allow for the following:

➤

2GB for Windows

➤

1GB for worker threads (576 3 2MB rounded down)

➤

512MB for linked servers, etc.

➤

1GB for the backup program and virus checker

For a total of 4.5GB, you would coni gure Max Server Memory to 59.5GB.

Both of these options can be valid in different circumstances. On a single SQL Server from which
you need to squeeze every drop of performance, you might use option 1 and monitor Total Server
Memory to see how often SQL Server has to give memory back to Windows. However, if you had
dozens of SQL Servers to manage or a mission-critical server, you might go with option 2, as it
would be easier to calculate across multiple servers and is less likely to cause a failure under
exceptional circumstances.