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


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

Continues…

Pages: 1 2 3 4 5




Related Articles :

  • No Related Articles Found

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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |