Schema (SQL Server 7.0)
| 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(32) | 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(85) | A GUID (globally unique identifier) 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 uniqueidentifier 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. |
| suid | SMALLINT | The user ID of the user running the process. |
Keeping a Table’s Data Pages in Cache
You can use the DBCC PINTABLE console command to keep a table’s data pages in memory.
Syntax
DBCC PINTABLE ( database_id , table_id )
Example
–Determine id number for the pubs database
–Clean up the display
SET NOCOUNT ON
–Use the pubs database
USE pubs
DECLARE @dbid INTEGER
SET @dbid = DB_ID(‘pubs’)
–Determine id number for the dbo.authors table
DECLARE @obid INTEGER
SET @obid = OBJECT_ID(‘dbo.authors’)
–Unpin the dbo.authors table to memory
DBCC PINTABLE (@dbid,@obid)
GO
Releasing a Pinned Table’s Data Pages From Memory
You can use the DBCC UNPINTABLE console command to release a pinned table’s data pages from memory.
Syntax
DBCC UNPINTABLE ( database_id , table_id )
Example
–Determine id number for the pubs database
–Clean up the display
SET NOCOUNT ON
–Use the pubs database
USE pubs
DECLARE @dbid INTEGER
SET @dbid = DB_ID(‘pubs’)
–Determine id number for the dbo.authors table
DECLARE @obid INTEGER
SET @obid = OBJECT_ID(‘dbo.authors’)
–Unpin the dbo.authors table to memory
DBCC UNPINTABLE (@dbid,@obid)
GO