sp_ps (A little more than "sp_who") 
Author Message
 sp_ps (A little more than "sp_who")

Here is a stored procedure which will provide a little
more info. than sp_who does.  It takes four optional
parameters, which are:

1) Number of times to repeat run (eg. sp_ps 5 to repeat 5 times)
2) Report stats for a particular user (eg. sp_ps 1, John)
3) Report stats for a particular host (eg. sp_ps 1, "%", galaxy)
4) Interval seconds (does not work yet).

Here is a sample output (It works best when isql has been started with a
-w100 [or greater]) and a window with width of 100 characters or more:
========================================================================
1> sp_ps
2> go

 User     Host     Progrm Pid    CPUsed DiskIO Mem Database Command  Status TableId     Locktype
 -------- -------- ------ ------ ------ ------ --- -------- -------- ------ ----------- --------
 JohnDoe  galaxy   Dynami 532       165      0   1 test     AWAITING sleepi        NULL     NULL
 MaryJane saturn   isql   7898       25      3   1 producti AWAITING sleepi        NULL     NULL  
 JillHill milkyway isql   20295      18      0   1 pubs     AWAITING sleepi        NULL     NULL  
 sa       jupiter  Sybase 794         1      0   1 master   AWAITING sleepi        NULL     NULL  

<STATS>
   BUSY:  (CpuBusy)   582650    (IoBusy)  1699055     (Idle)     1866
PACKETS:     (Sent)   875555  (Received)   179145   (Errors)       74
  TOTAL:    (Reads)  1499974    (Writes)  2005438   (Errors)        0

(return status = 0)
1>

Here is the code to create the stored proc (Good luck):
========================================================================

CREATE PROC sp_ps




    AS
/***********************************************************************
 *    Proc: sp_ps
 *  Author: Anil Hemrajani
 *    Date: March 5, 1993
 * Purpose: Sybase monitor stored procedure
 *   Notes: Does a 4-way join with SYBASE system tables
 **********************************************************************/
  BEGIN



     SET nocount on






     ELSE



       BEGIN
          SELECT DISTINCT
                 substring(a.name,1,8) "User",
                 substring(b.hostname,1,8) "Host",
                 substring(b.program_name,1,6) "Progrm",
                 substring(b.hostprocess,1,6) "Pid",
                 str(b.cpu,6) "CPUsed",
                 str(b.physical_io,6) "DiskIO",
                 str(b.memusage,3) "Mem",
                 substring(c.name,1,8) "Database",
                 substring(b.cmd,1,8) "Command",
                 substring(b.status,1,6) "Status",
                 d.id "TableId",
                 d.type "Locktype"
            FROM master..syslogins a,    master..sysprocesses b,
                 master..sysdatabases c, master..syslocks d

             AND a.suid        = b.suid

             AND b.dbid        = c.dbid
             AND b.spid       *= d.spid
             AND c.dbid       *= d.dbid
           ORDER BY a.name, b.hostname, b.hostprocess, c.name, d.id, b.cmd

         PRINT ""
         PRINT "<STATS>"













         PRINT ""



             WAITFOR DELAY "00:00:10"
       END
   END

===========================================================================
This has been tested in SQL Server v4.2, 4.8, and 4.9.1.

Anybody have any qoodies similar to this out there?



Tue, 19 Dec 1995 23:15:56 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. sp_ps (A little more than "sp_who") - Feedback?

2. sp_who and "CONDITIONAL" connection

3. Using sp_who to generate a "write"

4. "UNKNOWN TOKEN" in sp_who display

5. max of ("...","...","..")

6. "Client-less" database connections

7. DSN-less "ODBC" connections on Unix

8. TechTips: The little gotcha called "doDefault"

9. "DSN-less" connection

10. How to make a "decimal"-field to an "integer"-field

11. "."and ","

12. "."and ","


 
Powered by phpBB® Forum Software