SQLServerCentral Article

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.


Determining what is in 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:

Compiled Plan

Executable Plan

Parse Tree

Cursor Parse Tree

Extended Stored Procedure

objtype NVARCHAR(16) Object Type:

Stored Procedure

Prepared statement

Ad hoc query

ReplProc (replication procedure)

Trigger

View

Default

User table

System table

Check

Rule

objid INTEGER Object ID. Internally generated for ad-hoc queries or prepared statements.
dbid SMALLINT Database ID in which the object was compiled.
dbidexec SMALLINT Internal use.
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.
lasttime BIGINT Internal use.
maxexectime BIGINT Internal use.
avgexectime BIGINT Internal use.
lastreads BIGINT Internal use.
lastwrites BIGINT Internal use.
setopts INTEGER SET option settings modified for the object. Options include:

ANSI_PADDING

FORCEPLAN

CONCAT_NULL_YIELDS_NULL

ANSI_WARNINGS

ANSI_NULLS

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

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.

Schema (SQL Server 7.0)

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:

Compiled Plan

Executable Plan

Parse Tree

Cursor Parse Tree

Extended Stored Procedure

objtype NVARCHAR(16) Object Type:

Stored Procedure

Prepared statement

Ad hoc query

ReplProc (replication procedure)

Trigger

View

Default

User table

System table

Check

Rule

objid INTEGER Object ID. Internally generated for ad-hoc queries or prepared statements.
dbid SMALLINT Database ID in which the object was compiled.
dbidexec SMALLINT Internal use.
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:

ANSI_PADDING

FORCEPLAN

CONCAT_NULL_YIELDS_NULL

ANSI_WARNINGS

ANSI_NULLS

QUOTED_IDENTIFIER

ANSI_NULL_DFLT_ON

ANSI_NULL_DFLT_OFF

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.

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)

spid SMALLINT SQL Server Process ID
kpid SMALLINT Windows NT Thread ID
blocked SMALLINT SPID of blocking process
waittype BINARY(2) Reserved.
waittime INTEGER Current wait time, in milliseconds.

If 0 then process is not currently waiting.

lastwaittype NCHAR(32) The last or current wait type.
waitresource NCHAR(256) Lock resource.
dbid SMALLINT The database ID that the process is currently running in.
uid SMALLINT The ID of the user executing this process.
cpu INTEGER The cumulative CPU usage for this process.
physical_io BIGINT The cumulative Disk I/O for this process.
memusage INTEGER Number of pages in the procedure cache that are currently allocated to this process.

A negative number indicates that the process is taking memory away from another process.

login_time DATETIME The time the client process logged onto the server.

If the process is a system process the time is the time that SQL Server was last started.

last_batch DATETIME The last time the client process executed a remote stored procedure call or an EXECUTE statement.

If the process is a system process the time is the time that SQL Server was last started.

ecid SMALLINT Execution context ID used to identify all subthreads.
open_tran SMALLINT The number of open transactions the process holds.
status NCHAR(30) The current status of the process.
sid BINARY(86) A GUID (globally uniqueidentifier) for the user.
hostname NCHAR(128) The name of the workstation running the process.
program_name NCHAR(128) The name of the application running the process.
hostprocess NCHAR(8) The workstation SPID number.
cmd NCHAR(16) The command currently being executed.
nt_domain NCHAR(128) The Windows Domain name for the client if the process is using Windows Authentication or a trusted connection.
nt_username NCHAR(128) The Windows user name running the process if the process is using Windows Authentication or a trusted connection.
net_address NCHAR(12) The assigned unique identifier number for each of the client's network cards.
net_library NCHAR(12) The client's network library.
loginame NCHAR(128) The client's login name.
context_info BINARY(128) Internal Use.

Schema (SQL Server 7.0)

spid SMALLINT SQL Server Process ID
kpid SMALLINT Windows NT Thread ID
blocked SMALLINT SPID of blocking process
waittype BINARY(2) Reserved.
waittime INTEGER Current wait time, in milliseconds.

If 0 then process is not currently waiting.

lastwaittype NCHAR(32) The last or current wait type.
waitresource NCHAR(32) Lock resource.
dbid SMALLINT The database ID that the process is currently running in.
uid SMALLINT The ID of the user executing this process.
cpu INTEGER The cumulative CPU usage for this process.
physical_io BIGINT The cumulative Disk I/O for this process.
memusage INTEGER Number of pages in the procedure cache that are currently allocated to this process.

A negative number indicates that the process is taking memory away from another process.

login_time DATETIME The time the client process logged onto the server.

If the process is a system process the time is the time that SQL Server was last started.

last_batch DATETIME The last time the client process executed a remote stored procedure call or an EXECUTE statement.

If the process is a system process the time is the time that SQL Server was last started.

ecid SMALLINT Execution context ID used to identify all subthreads.
open_tran SMALLINT The number of open transactions the process holds.
status NCHAR(30) The current status of the process.
sid BINARY(85) A GUID (globally unique identifier) for the user.
hostname NCHAR(128) The name of the workstation running the process.
program_name NCHAR(128) The name of the application running the process.
hostprocess NCHAR(8) The workstation SPID number.
cmd NCHAR(16) The command currently being executed.
nt_domain NCHAR(128) The Windows Domain name for the client if the process is using Windows Authentication or a trusted connection.
nt_username NCHAR(128) The Windows user name running the process if the process is using Windows Authentication or a trusted connection.
net_address NCHAR(12) The assigned uniqueidentifier number for each of the client's network cards.
net_library NCHAR(12) The client's network library.
loginame NCHAR(128) The client's login name.
suid SMALLINT The user ID of the user running the process.

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

Rate

5 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (4)

You rated this post out of 5. Change rating