syscacheobjects

Transact-SQL Reference

previous page next page
Transact-SQL Reference

syscacheobjects

Contains information about how the cache is used. syscacheobjects belongs to the master database. The following table shows cache lookup keys.

Column name Data type Description
bucketid int Bucket ID. Value indicates a range from 0 through (directory size - 1). Directory size is the size of the hash table.
cacheobjtype nvarchar(34) Type of object in the cache:

Compiled Plan
Executable Plan
Parse Tree
Cursor Parse Tree
Extended Stored Procedure
objtype nvarchar(16) Type of object:

Stored Procedure
Prepared statement
Ad hoc query (Transact-SQL submitted as language events from isql or osql, as opposed to remote procedure calls)
ReplProc (replication procedure)
Trigger
View
Default
User table
System table
Check
Rule
objid int One of the main keys used for looking up an object in the cache. This is the object ID stored in sysobjects for database objects (procedures, views, triggers, and so on). For cache objects such as ad hoc or prepared SQL, objid is an internally generated value.
dbid smallint Database ID in which the cache object was compiled.
uid smallint Indicates the creator of the plan for ad hoc query plans and prepared plans. -2 indicates the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.
refcounts int Number of other cache objects referencing this cache object. A count of 1 is the base.
usecounts int Number of times this cache object has been used since inception.
pagesused int Number of memory pages consumed by the cache object.
setopts int SET option settings that affect a compiled plan. These are part of the cache key. Changes to values in this column indicate users have modified SET options. These 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 Language ID. ID of the language of the connection that created the cache object.
dateformat smallint Date format of the connection that created the cache object.
status int Indicates whether the cache object is a cursor plan. Currently, only the least significant bit is used.
sqlbytes int Length of name or batch submitted. Can be used to distinguish two names or submitted batches if the first 128 characters are the same.
sql nvarchar(256) Procedure name or first 128 characters of the batch submitted.

previous page start next page