Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jul 31, 2000

Use Undocumented SP_WHO2 Procedure Instead of SP_WHO for SQL Server 7.0

The sp_who internal procedure allows users to view current activity on the database. This command provides a view into several system tables (e.g., syslocks, sysprocesses, etc.). The sp_who command returns the following information:
  • Spid—The system process ID.
  • status—The status of the process (e.g., RUNNABLE, SLEEPING).
  • loginame—Login name of the user.
  • hostname—Machine name of the user.
  • blk—If the process is getting blocked, this value is the SPID of the blocking process.
  • dbname—Name of database the process is using.
  • Cmd—The command currently being executed (e.g., SELECT, INSERT)
The sp_who2 internal procedure provides the above information, but also provides the following additional information:
  • CPUTime—Total CPU time the process has taken.
  • DiskIO—Total amount of disk reads for the process.
  • LastBatch—Last time a client called a procedure or executed a query.
  • ProgramName—Application that has initiated the connection (e.g., Visual Basic, MS SQL Query Analyzer)
The sp_who2 procedure is not documented in MS SQL Server 7.0 Books Online. Using sp_who2 enables SQL Server users to better analyze database processes and more easily identify which application is associated with each process.
Andrew Holliday
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap