How to Interact with SQL Server’s Data and Procedure Cache

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)
User table
System table
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:
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.


Pages: 1 2 3 4 5


No comments yet... Be the first to leave a reply!