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. |
Continues…