Microsoft KB Archive/63929

= INFO: SYSPROCESSES Table to Display Resource Information =

Article ID: 63929

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition
 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q63929



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

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:

170360 INF: Information on CPU and Physical_io Columns of Sysprocesses

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 &quot;sysprocesses (T-SQL)&quot;).

Keywords: kbinfo kbprogramming KB63929

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.