INF: SYSPROCESSES Table to Display Resource Information

  - Microsoft SQL Server version 4.2 for OS/2

SUMMARY

This article describes how to see the distribution of resources among various users of SQL Server.

MORE INFORMATION

There is a table named SYSPROCESSES that contains the necessary information to do this. It is not a stored table; SQL Server constructs it when you query it. This table does not give CPU and physical I/O in percentages, but in absolute numbers. You can calculate percentages from these numbers.

Listed below is a query that shows the login name, program, CPU, and I/O information:

   select l.name, p.program_name, p.cpu, p.physical_io
   from sysprocesses p, syslogins l
   where p.cpu>0 and
   l.suid=p.suid

   select l.name, p.program_name,
     (p.cpu*100)/(select sum(p.cpu) from sysprocesses),
     (p.physical_io*100)/(select sum(p.physical_io) from sysprocesses)
   from sysprocesses p, syslogins l
   where p.cpu>0 and
   l.suid=p.suid