INF: SYSPROCESSES Table to Display Resource Information

ID: Q63929

The information in this article applies to:

Microsoft SQL Server for OS/2, version 4.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

To see percentages, you can use the following query:


   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

Keywords : kbprg SSrvAdmin SSrvServer
Version : 4.2
Platform : OS/2
Issue type :