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

The RESOURCE_SEMAPHORE wait type is a wait on a memory grant, so if you see this near the top
in your results from the sys.dm_os_wait_stats DMV, then your system is struggling to provide
memory grants fast enough.

You can also encounter performance issues other than just a query timing out while it waits for
a memory grant. Within an execution plan or when analyzing a SQL trace, you may notice hash
warning or sort warning messages if you have selected the relevant events. These occur when the
memory grant was insufi cient for a query's requirements.

A hash warning occurs when the hash build doesn't i t in memory and must be spilled to disk
(its actually written to tempdb). A sort warning occurs when a multi-pass sort is required because
the granted memory was insufi cient. Both warnings generally occur because the SQL Server Query
Optimizer made the wrong choice, usually because of inaccurate statistics or a lack of useful
statistics. For more details about this, see Chapter 5.

OPTIMIZING SQL SERVER MEMORY CONFIGURATION

This section discusses some of the most common memory coni guration options for SQL Server.

Min and Max Server Memory
Min Server Memory (MB) and Max Server Memory (MB) control the allowable size of all SQL
Server's memory usage. With the introduction of a new Memory Manager described earlier in the
chapter, this is a change for SQL Server 2012. This makes sizing SQL Server's memory requirements
much easier than with previous versions.

As its name suggests, Min Server Memory controls the minimum amount of physical memory that
SQL Server will try to keep committed. We say “try” because it can fall under that value if Windows
is desperate enough, but to all intents and purposes it sets a l oor for SQL Server's memory usage.

When the SQL Server service starts, it does not acquire all the memory coni gured in Min Server
Memory but instead starts with only the minimum required, growing as necessary. Once memory
usage has increased beyond the Min Server Memory setting, SQL Server won't release any
memory below that amount.

Not surprisingly, Max Server Memory is the opposite of Min Server Memory, setting a ceiling for
memory usage. Both values can be set using sp_configure or through Management Studio on the
Memory page of the SQL Server Properties window.

Coni guring a maximum value for the buffer pool is the more important of the two settings and
will prevent SQL Server from taking too much memory. This is particularly signii cant on 64-bit
systems, where a lack of free physical memory can cause Windows to trim SQL Server's working set.
See the section “Lock Pages in Memory” for a full description of this issue.

There are several different ways to calculate an appropriate value for coni guring Max Server
Memory, but two of the most straightforward are as follows:
➤

Look at SQL Server's maximum usage.

➤

Determine the maximum potential for memory requirements outside SQL Server.