sys.syscacheobjects (Transact-SQL)

  • Article

Contains information about how the cache is used.

Important

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

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(17)

Type of object in the cache:

  • Compiled plan

  • Executable plan

  • Parse tree

  • Cursor

  • Extended stored procedure

objtype

nvarchar(8)

Type of object:

  • Stored procedure

  • Prepared statement

  • Ad hoc query (Transact-SQL submitted as language events from the sqlcmd or osql utilities, instead of 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.

dbidexec

smallint

Database ID from which the query is executed.

For most objects, dbidexec has the same value as dbid.

For system views, dbidexec is the database ID from which the query is executed.

For ad hoc queries, dbidexec is 0. This means dbidexec has the same value as dbid.

uid

smallint

Indicates the creator of the plan for ad hoc query plans and prepared plans.

-2 = 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.

Overflows or returns NULL if the number of users and roles exceeds 32,767.

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 pages consumed by the cache object.

setopts

int

SET option settings that affect a compiled plan. These settings are part of the cache key. Changes to values in this column indicate users have modified SET options.

These options include the following:

  • 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.

lasttime

bigint

For backward compatibility only. Always returns 0.

maxexectime

bigint

For backward compatibility only. Always returns 0.

avgexectime

bigint

For backward compatibility only. Always returns 0.

lastreads

bigint

For backward compatibility only. Always returns 0.

lastwrites

bigint

For backward compatibility only. Always returns 0.

sqlbytes

int

Length in bytes of the procedure definition or batch submitted.

sql

nvarchar(3900)

Module definition or the first 3900 characters of the batch submitted.

See Also

Reference

Compatibility Views (Transact-SQL)