As you work with SQL Server you have probably heard of the terms data cache and procedure cache and may have wondered what exactly a cache was. SQL Server is configure to use a pool of memory of the server and it will allocate the majority of this memory pool to hold both data pages that have been read and the compiled execution plans for all Transact-SQL statements. It is this dynamic pool of memory that is being referred to and the data cache and procedure cache, keep in mind that in versions of SQL Server before SQL Server 7.0 the data cache and procedure cache were two separate pools of memory and could be controlled separately, today one pool of memory is used both for data and execution plans.

     SQL Server will manage the objects in its cache in a few main ways: freeing up buffers or aging execution plans. A buffer is a page in memory that is the same size as a data or index page and is used to hold one page of data from the database. The buffer pool is managed by a process called the lazywriter, this lazywriter uses a clock algorithm to sweep through the buffer pool and free up any clean buffers to keep a supply of buffers empty for the next set of data pages. As the lazywriter visits each buffer it will determine whether that buffer has been referenced since the last lazywriter sweep, it does this by examining a reference count value in the buffer header, the reference count is adjusted up by 1 each time a statement references that buffer. If the reference count is not 0, the buffer will stay in the pool, but its reference count will be adjusted downward for the next sweep. To make this downward adjustment the lazywriter will divide the reference counter in the buffer page header by 4 and discard the remainder. When the reference counter goes to 0, the dirty page indicator is checked and if the page is dirty(modifications have been made to the data since the data page was placed in memory), a write is scheduled to write the modifications to disk. The lazywriter will also sweep the buffer pool when the number of pages on the free list falls below a minimum value, this value is computed as a percentage of the overall buffer pool size but is always between 128KB and 4MB. SQL Server will adjust this computed size based on the load on the system and the number of buffer stalls occurring. A buffer stall is when a process needs a buffer to hold data but none are available. This process will be go to sleep until the lazywriter can free some buffers. If the number of stalls increased to more than a few a second then SQL Server will adjust the computed size of the free list upward, the computed size will be adjusted downward if the load is light and very few buffer stalls are occurring.

     SQL Server will also manage the cache by aging execution plans. Execution plans used to just mean the execution plans compiled for stored procedures, but with SQL Server 2000 these execution plans can also refer to ad-hoc SQL statement plans, an ad-hoc SQL statement is basically any statement that is not a stored procedure, an autoparameterizex query, a sp_executesql statement or a statement prepared and executed with the ODBC/OLE DB SQLPrepare/SQLExecute or ICommandPrepare commands. Once an execution plan is compiled the plan will be saved to the cache along with a cost factor that is determined by the cost of actually creating the plan, this value will be set to 0 if the statement was an ad-hoc statement and to the actual cost if the plan is not for an ad-hoc statement, the cost is largely the I/O needed to compile the plan. A 0 cost factor value means that the plan can be immediately dropped from the cache. SQL Server's lazywriter will sweep the cache and deallocate the execution plan if the memory manager requires memory and all available memory is currently in use, if the cost factor value is 0 and if the object is not currently referenced by a connection. Execution plans, even ad-hoc plans, can stay in memory until SQL Server is shut down if another process determines it can use the compiled plan and the plan is constantly being reused. Ad-hoc plans will have their cost factor value increased by 1 each time it is reused, the highest ad-hoc cost factor value can go is its actual cost to compile. Non ad-hoc plans will the cost factor value set back to their original compile cost values.

Copyright 2002 by Randy Dyess, All rights Reserved