Database Reference
In-Depth Information
As DBAs, we know firsthand that databases by their nature will consume as much
memory as we give them. When a database consumes all the available memory,
database performance will be severely impacted. By the database consuming all
available memory, it starves the operating system from the resources it needs, causing
the OS to page and swap. To prevent this from happening, it's important that you
configure Max Server Memory properly. So even though we say you have two options,
you really only have one.
SQL Server Max Server Memory: Common Misperception
A common misperception is that when SQL Server starts, it grabs all the memory
allocated to it with Max Server Memory. In reality, SQL Server will only request the
memory it needs to initialize the database and will acquire additional memory as
required. The SQL Server engine will not release memory until the minimum threshold
has been reached, and it will not acquire memory above and beyond the Max Server
Memory setting.
An excellent Microsoft TechNet article titled “Effects of Min and Max Server Memory”
can be found at
http://technet.microsoft.com/en-
Formula for Configuring Max Server Memory
At Ntirety, our consulting services team has developed a simple formula based on our
experience that gets you to an appropriate setting for Max Server Memory. Before I
share the formula, it's important to remember there are no hard-and-fast rules. Each
particular situation has nuances you have to account for. Also, a number of other factors
impact how much memory you need to leave for the Windows operating system. Here
are some examples:
Are big copy jobs being performed periodically?
Are a lot of extended stored procedures in use? Remember, they operate in the
free memory segment.
Is the virtual machine also acting as a web server or application server?
This formula is a starting guideline that should be appropriate for the majority of
situations. Once it is implemented, we recommend following up by reviewing SQL
buffer pool performance (page life expectancy) and the available system RAM to see if
any tweaks are needed (up or down) for Max Server Memory.
Based on our experience, we recommend you use the following formula:
Assigned VM Memory - (2GB + Additional 1GB per 16GB Physical Memory) =
SQL Max Memory
To make this a little easier,
Figure 7.8
serves as a quick reference guide for where to
