Jasinski Technical Wiki


Home Page
All Pages

Quick Search
Advanced Search »

Contributor Links

Create a new Page
File Management
Your Profile

Other Wiki Sections



syscacheobjects System Table - SQL Server 2000

Modified on Fri, May 01, 2009, 10:50 AM by Administrator Categorized as SQL Server System Tables
Contains information about how the cache is used. This table belongs to the master database. The following table shows cache lookup keys.

Column nameData typeDescription
bucketidintBucket ID. Value indicates a range from 0 through (directory size - 1). Directory size is the size of the hash table.
cacheobjtypenvarchar(34)Type of object in the cache: Compiled Plan; Executable Plan; Parse Tree; Cursor Parse Tree; Extended Stored Procedure.
objtypenvarchar(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.
objidintOne 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.
dbidsmallintDatabase ID in which the cache object was compiled.
uidsmallintIndicates 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.
refcountsintNumber of other cache objects referencing this cache object. A count of 1 is the base.
usecountsintNumber of times this cache object has been used since inception.
pagesusedintNumber of memory pages consumed by the cache object.
setoptsintSET 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.
langidsmallintLanguage ID. ID of the language of the connection that created the cache object.
dateformatsmallintDate format of the connection that created the cache object.
statusintIndicates whether the cache object is a cursor plan. Currently, only the least significant bit is used.
sqlbytesintLength of name or batch submitted. Can be used to distinguish two names or submitted batches if the first 128 characters are the same.
sqlnvarchar(256)Procedure name or first 128 characters of the batch submitted.

ScrewTurn Wiki version Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2023, Patrick Jasinski.