How to Interact with SQL Server’s Data and Procedure Cache
As you have worked with SQL Server, you probably have run across the terms data cache and procedure cache, and may have wondered what exactly a cache was. SQL Server is configured to use a physical pool of memory on the server, and it will allocate the majority of this memory pool to hold data pages that have been read, along with the compiled execution plans for all previously-run Transact-SQL statements. It is this dynamic pool of memory that is being referred to by the data cache and procedure cache. Before SQL Server 7.0, the data cache and procedure cache were two separate pools of memory and could be controlled separately. In SQL Server 7.0 and SQL Server 2000, one pool of memory is used both for data and execution plans.
SQL Server manages the objects in its cache two 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 in order to keep a supply of buffers empty for the next set of data pages.
As the lazywriter visits each buffer, it determines 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 divides the reference counter in the buffer page header by 4 and discards 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 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 goes to sleep until the lazywriter can free some buffers. If the number of stalls increases 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 also manages the cache by aging execution plans. Execution plans used to just mean the execution plans compiled for stored procedures. But with SQL Server 2000, 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 is saved to the cache along with a cost factor that is determined by the cost of actually creating the plan. This value is set to 0 if the statement is 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 sweeps the cache and deallocates 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.
Now that I have you completely confused on what data cache, procedure cache, buffers and the lazywriter is, let’s take a look at some ways that a Transact-SQL programmer can interact with the cache.
Determining What Is In the Cache
You can query the syscacheobjects system table to determine what objects are currently in cache.
Schema (SQL Server 2000)
|bucketid||INTEGER||Bucket ID. Value indicates a range from 0 through (directory size – 1). Directory size is the size of the hash table.|
|cacheobjtype||NVARCHAR(34)||Object Type in cache:
Cursor Parse Tree
Extended Stored Procedure
Ad hoc query
ReplProc (replication procedure)
|objid||INTEGER||Object ID. Internally generated for ad-hoc queries or prepared statements.|
|dbid||SMALLINT||Database ID in which the object was compiled.|
|uid||SMALLINT||The creator ID of the plan for ad hoc query plans and prepared plans. A -2 indicates the batch submitted does not depend on implicit name resolution and can be shared among different users.|
|refcounts||INTEGER||Number of other cached objects referencing this object. 1 is the base number.|
|usecounts||INTEGER||Number of times this cached objects has been used since cached.|
|pagesused||INTEGER||Number of memory pages used by this object.|
|setopts||INTEGER||SET option settings modified for the object. Options include:
|langid||SMALLINT||The language ID the connection that created the cache object.|
|dateformat||SMALLINT||Date format of the connection that created the cache object.|
|status||INTEGER||Indicates whether the cache object is a cursor plan.|
|sqlbytes||INTEGER||Length of name or batch submitted.|
|sql||NVARCHAR(256)||Procedure name or characters of the batch submitted.|