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.

Determining what is in cache.

You can query the syscacheobjects system table to determine what objects are currently in cache.

Schema (SQL Server 2000)

Schema (SQL Server 7.0)

Determining the number of pages in cache that are allocated to a process.

You can query the sysprocesses system table to determine the number of pages a process currently has allocated in the cache.

Schema (SQL Server 2000)

Schema (SQL Server 7.0)

Keeping a table's data pages in cache.

You can use the DBCC PINTABLE console command to keep a table's data pages in memory.

Syntax

DBCC PINTABLE ( database_id , table_id )

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

Releasing a pinned table's data pages from memory.

You can use the DBCC UNPINTABLE console command to release a pinned table's data pages from memory.

Syntax

DBCC UNPINTABLE ( database_id , table_id )

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

Forcing all dirty pages to be written to a disk.

You can use the CHECKPOINT statement to force all dirty pages to be written to disk.

Syntax

CHECKPOINT

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

Releasing all data pages from memory.

You can use the DBCC DROPCLEANBUFFERS console command to release all data pages from memory.

Syntax

DBCC DROPCLEANBUFFERS

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

Print out the buffer headers and pages from the buffer cache.

You can use the DBCC BUFFER console command to print out the buffer headers and pages from the buffer cache.

Syntax

DBCC BUFFER ( [@dbid|'@dbname' ]

[,@objid|obname ] [,@numofbuffers]

[,@printopt] )

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

Displaying the stored procedure cache.

The contents of the stored procedure cache can return with the DBCC PROCCACHE console command.

Syntax

DBCC PROCCACHE

Example
--Return the procedure cache information
DBCC PROCCACHE
GO

Remove all execution plans from cache.

You can use the DBCC FREEPROCCACHE console command or the DBCC FLUSHPROCINDB console command to flush the stored procedure cache and cause all stored procedures to recompile the next time they are executed.

Syntax

DBCC FLUSHPROCINDB (@dbid)

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)

Syntax

DBCC FREEPROCCACHE

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

Showing memory usage.

You can use the DBCC MEMORYSTATUS console command to view memory usage.

SYNTAX

DBCC MEMORYSTATUS

EXAMPLE
--Clean up the display
SET NOCOUNT ON
--Use the master database
USE master
--Show memory usage
DBCC MEMORYSTATUS
GO

Interacting with SQL Server's cache as a programmer is not often needed, but when you do need to determine what is going on with the cache or you simpily need to flush the execution plans or data pages to tune a query you now have the means to do so. Explore the use of the above statements and system tables to round out your knowledge and impress others when there is a problem with the cache and you know exactly what command or system table to use to troubleshoot the error.

For further explanation of the above commands and system tables that are documented see either SQL Server's Books Online or to see further explanation of all the above commands and system tables, download and purchase Transact-SQL Language Reference Guide from my website.

Copyright 2002 by Randy Dyess, All rights Reserved