Memory Management in SQL Server

Introduction: I have heard people asking me how SQL Server manages memory. The answer to this was very difficult with SQL Server earlier versions. And with SQL Server 2005 it has been simpler to explain and understand. SQL Server by default installation manages memory dynamically and has settings that it can well negotiate with the operating system for the need of memory. Let us look into the depth of the SQL Server memory management. Before moving into it, let us look the important terms associated with SQL Server memory.  

 

BUFFER CACHE: Caching is a process of storing data from the disk in memory. SQL Server stores the data that it has read from the disk in the buffer cache so that the next time the same data is read it does not need an IO cycle to process and instead it can get the data from the cache. When the data is in the cache and request succeed then it is a CACHE HIT and when it fails it is a CACHE MISS. The BUFFER CACHE HIT ratio is calculated with these terms. FREE LIST: SQL Server internally manages a data structure called free lists which has the address of all the pages that are not allocated to any of the data and index pages in the buffer cache. When SQL Server reads data from the memory the data is placed on the first free page that is available in the list. Each page contains page header information that stores the data about the page and its internals.  

CHECKPOINT PROCESS: This process checks for the modified pages in the buffer cache and moves it to the disk so that reading dirty pages can be avoided. The checkpoint process is governed by two parameters. 1. Checkpoint interval 2. recovery interval While the checkpoint interval directly affects the interval between two check points, the recovery interval determines the checkpoint in basis of how fast the database should be recovered in case of a system restart or failure. The SQL Server dynamically calculates the checkpoint interval depending upon the recovery interval parameter. Also it should be noted that having a checkpoint interval of low value will affect the server performance causing a slow down while having a high value can make the database to data loss in some cases. The following events happen when checkpoint occurs a. Writing all dirty data from buffer to disk b. Writing all active transaction to the transaction log c. Storing checkpoint records in the log Normally checkpoints occur for the following events in the database a. When a checkpoint command is issued manually b. When server is SHUTDOWN c. When you use ALTER DATABASE command to modify the data files d. When you issue a backup command e. When the recovery model is changed f. When you use BULK COPY

 

 LAZY WRITER PROCESS: Lazy writer process also does the same as checkpoint process i.e. writing the modified pages to the disk but in addition the LAZY WRITER process checks for the amount of free space in the buffer cache and if it finds to be a value below the range that it should be then this process cleans up the pages that are being least used so that the free list in the buffer cache is always above the value required. Now having known these important terms about the SQL Server memory internals let us go and look onto how SQL Server manages memory within itself. With SQL Server 2005 memory allocation is dynamic even when AWE is enabled. The only exception to this is having SQL Server 2005 in 32-bit operating system with AWE enabled which has a static memory allocation means that SQL Server grabs memory and does not release it to the other processes. By default SQL Server manages memory dynamically in which case only necessary memory is allocated when SQL Server starts up and acquires more memory as and when needed. When it does not need the extra memory SQL Server deallocates to the memory to be used by other applications. This dynamic setting can be overridden by setting the SQL Server to a configured value so that it only uses that much amount of memory.  

 

Setting MIN AND MAX MEMORY FOR SQL SERVER: You need to use the sp_configure procedure to configure the memory for SQL Server. Since these configuration parameters are advanced you need to have the option enabled using 

 

1 2	Sp_configure 'show advanced options',1 GO

Then you can set the minimum and maximum memory using

1 2 3 4 5 6

Sp_configure 'MIN SERVER MEMORY', (memory VALUE IN MB) RECONFIGURE GO Sp_configure 'MAX SERVER MEMORY', (memory VALUE IN MB) RECONFIGURE GO

To enable AWE option use

1 2 3

Sp_configure 'awe enabled' ,1 RECONFIGURE GO

This setting requires SQL Server to be restarted to takes effect. Microsoft recommends allowing SQL Server to handle memory dynamically. But yet these settings can be useful when you run multiple instances in a single box allocating fixed memory for each instance. The given table below is the amount of memory that each edition of SQL Server can use.  

SQL Server EDITION	MAXIMUM SIUPPORTED 32-bit	MAXIMUM SIUPPORTED 64-bit
Express	1 GB	Not applicable
Workgroup	3 GB	Not applicable
Standard	OS MAX	32 TB
Developer	OS MAX	32 TB
Enterprise	OS MAX	OS MAX

If you feel that your server has more then 4 GB of RAM and SQL Server uses only little check to see if you have /3GB (Click Here to get the code) switch added in the boot.ini file in the operating system and in SQL Server if AWE is enabled. Hope you would have had a happy reading about SQL Server memory management.

Read more: http://sql-articles.com/articles/performance-tunning/memory-management-in-sql-server/#ixzz2TuOBFIte