Sql Server Memory 101

[Copied over from my original blog https://ssinghal.spaces.live.com to have everything under one roof]

Recently a customer wanted to know some details on how SqlServer manges memory, sort of the basics. Presented below is some of the starter resources for learning about it, I would be happy to hear any specific questions/comments that anyone has after going thru the material below.

thanks

Saurabh

The following blog from Slava is a very good starting point that explains the basics of memory management in Sql Server 2005:

https://blogs.msdn.com/slavao/archive/2005/02/11/371063.aspx

So in effect:

SQL Server 2005 dynamically acquires and frees memory as required and typically, an administrator does not have to specify how much memory should be allocated to SQL Server. Nonetheless SQL Server provides two memory settings (max and min server memory BOL link ) that the user can control using sp_configure. These control the size of buffer pool, which is the preferable provider for all dynamic allocations inside Sql Server. Besides that Sql Server also reacts to VAS pressure on the box or any external physical memory pressure signaled by underlying Windows OS on the box (details of which you can find at https://blogs.msdn.com/slavao/archive/2005/02/19/376714.aspx)

More from BOL:

“When SQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires (commits) only the required amount of physical memory for the current load.

The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.

As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.”

For further details, you can refer to following article: https://msdn2.microsoft.com/en-us/library/aa337525.aspx