Interact with SQL Server's Data and Procedure Cache

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.

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.

Example
--Determine id number for the pubs database 
--Clean up the display
SET NOCOUNT ON

--Use the pubs database
USE pubs

DECLARE @dbid INTEGER
SET @dbid = DB_ID('pubs')

--Determine id number for the dbo.authors table
DECLARE @obid INTEGER
SET @obid = OBJECT_ID('dbo.authors')

--Unpin the dbo.authors table to memory
DBCC UNPINTABLE (@dbid,@obid)
GO

Example
--Determine id number for the pubs database 
--Clean up the display
SET NOCOUNT ON

--Use the pubs database
USE pubs

DECLARE @dbid INTEGER
SET @dbid = DB_ID('pubs')

--Determine id number for the dbo.authors table
DECLARE @obid INTEGER
SET @obid = OBJECT_ID('dbo.authors')

--Unpin the dbo.authors table to memory
DBCC UNPINTABLE (@dbid,@obid)
GO

Example
--Force a database checkpoint
PRINT 'Force a Checkpoint'
CHECKPOINT
GO

Example
--Remove the clean buffers from the buffer pool
--Clean up the display
SET NOCOUNT ON

DBCC DROPCLEANBUFFERS
GO

Example
--Dump contents of buffers
--Clean up the display
SET NOCOUNT ON

--Trace Flag 3604 must be on in order for DBCC command to run
DBCC TRACEON (3604)

--Use the pubs database
USE pubs

--Determine and hold database id for the pubs database
DECLARE @numDBID INTEGER
SET @numDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'pubs')

--Determine and hold object id for the authors table
DECLARE @numOBID INTEGER
SET @numOBID = OBJECT_ID('dbo.authors')

--Fill buffers
SELECT COUNT(*) FROM dbo.authors

--Display buffers
DBCC BUFFER (@numDBID ,@numOBID,1, 1 )
GO

Example
--Return the procedure cache information
DBCC PROCCACHE
GO

Example
--Recompile all stored procedures in the pubs database
--Clean up the display
SET NOCOUNT ON

--Determine the id of the pubs database
DECLARE @intDBID INTEGER
SET @intDBID = (SELECT dbid FROM master.dbo.sysdatabases WHERE name = 'pubs')

--Recompile the stored procedures
DBCC FLUSHPROCINDB (@intDBID)

Example
--Remove all elements from the procedure cache
--Clean up the display
SET NOCOUNT ON

DBCC FREEPROCCACHE
GO

EXAMPLE
--Clean up the display
SET NOCOUNT ON

--Use the master database
USE master

--Show memory usage
DBCC MEMORYSTATUS
GO