INFO: SYSPROCESSES Table to Display Resource Information (63929)

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
				

For additional information about the sysprocesses cpu and physical_io columns in SQL Server 6.0 and 6.5, click the article number below to view the article in the Microsoft Knowledge Base: This technique is not generally applicable to SQL Server 7.0 where the cpu column of sysprocesses is only updated for Transact-SQL statements run in or by a connection that has enabled SET STATISTICS TIME ON (see the SQL Server 7.0 Books Online article "sysprocesses (T-SQL)").