SQLUNINTERRUPTED

Previous Posts in this series

SQL Server Memory– Underground

In my Previous post, I had talked briefly about the windows Memory Management and how SQL calculates its Buffer Pool and MTL. In this post, I would be talking mainly about the workings of the SQL Buffer Pool and the MTL, and how to figure out the memory usage by SQL and its components. This post would be specific to SQL 2000 unless specified otherwise.

SQL Buffer Pool

SQL Memory Management is Dynamic in nature, meaning SQL can allocate or de-allocate memory in the Buffer Pool as per requirement. In order for the SQL Server to effectively manage its memory allocations SQL Server 7, introduced a layer of Memory Manager that manage various types of memory requests in the SQL Buffer pool.

The important ones are

1. General – This memory manager handles general allocation (not handled by other managers). Includes things like locks etc..
2. Query Plan – As the name implies, this is mainly used for query plans.
3. Optimization – Handles memory requests during optimization.
4. Utility – Manages Memory allocations for different utilities, like tracing, log shipping etc.
5. Connection – Manages memory allocations for Connections. This includes memory for the input and output buffers.

The idea behind using these managers is very simple. Consider an Analogy. In a company with 1000 employees, If there is only one manager, management becomes really difficult. One the second hand if we have 10 managers managing 100 employees each, it would be easier. With multiple managers it becomes easier to manage and report the usage.

The amount of memory being consumed (out of the 2 GB user mode VAS), can be easily tracked using either of the following options.

• DBCC MEMORYSTATUS
• Windows Performance Monitor Counters

DBCC MEMORYSTAUS

The KB article describes the output of the DBCC MEMORYSTATUS command for SQL 2000 in great detail. The following section of the DBCC MEMORYSTATUS output can be used to track the usage per Memory Manager.

The OS reserved/OS committed/OS in Use sections are for all allocations which have been made in the SQL VAS  by the memory managers (more than 8 KB allocations) and also for all external allocations coming from external components loaded in the SQL Space. In short, this can be used to track all the allocations made in the Mem-To-Leave area.

Apart from the output for the Memory Managers, another important section in the output is the first section of the output, which can be used to see how are the Database Pages allocated in the BPool.

Windows Performance Monitor Counters

SQL Server exposes two Performance Monitor Objects for monitoring SQL Server Memory. These performance Monitor counters are also applicable to next releases of SQL Server (SQL 2005/2008/2008 R2) with slight differences, which I would be pointing out in the next posts.

• Memory Manager: Provides information about SQL Server memory as whole.
• Buffer Manager: Provides information about the SQL Buffer Pool

Memory Manager

The important counters under this object are

1. Connection Memory: Indicates the amount of memory being consumed under the Connection Memory Manager.
2. Granted Workspace Memory: Memory allocated/reserved for operations during Query Execution. All memory for sorting, hashing come from these.
3. Memory Grants Pending: Number of memory requests which have not been completed. A non-zero value for a sustained period would indicate a memory pressure in SQL Server.
4. Optimizer Memory: Memory being consumed under the Optimizer Memory Manager.
5. Total Server Memory: Total memory consumed my SQL. This included both allocation in BPool and Allocations in Mem-To-Leave. This value should be equal to the sum of the “OS in Use” under dynamic memory manager, and “Committed” under Buffer Count sections of the Memory Status values. Remember these values in Memory Status output are in number of 8 KB pages.

Buffer Manager

1. Checkpoint Pages/Sec: Number of pages being flushed by the checkpoint thread.
2. Database Pages: Number of BPool buffers being used for Database pages.
3. Free Pages: Number of pages free in the buffer pool. This values should always be greater than 0.
4. Lazy Writes/Sec: Number of lazy write operations per sec.
5. Stolen Pages/Sec: Committed pages which are not being as a database page are termed as stolen.
6. Total Pages: Number of pages currently being used in the Buffer Pool.

Memory discussions for SQL Server is not complete without the mention of two very important operations, Checkpoint and Lazy Writer.

Checkpoint: Checkpoint is the process wherein Dirty Buffer (database pages which have been modified by a DML operation) back to the disk.

Lazy Writer: The sole purpose of Lazy Writer is to maintain some free buffers in the SQL Server Buffer Pool. Lazy writer runs periodically and check which buffers can be flushed and returned to the free pool.