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) 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…
No comments yet... Be the first to leave a reply!