To identify the applications running on your system and the users who are running them, query the sysprocesses system table in the master database.
The following query determines that isql, payroll, perl, and acctng are the only client programs whose names were passed to the Adaptive Server:
select spid, cpu, physical_io, substring(user_name(uid),1,10) user_name, hostname, program_name, cmd from sysprocesses
spid cpu physical_io user_name hostname program_name cmd ---- --- ----------- --------- -------- ------------ ------ 17 4 12748 dbo sabrina isql SELECT 424 5 0 dbo HOWELL isql UPDATE 526 0 365 joe scotty payroll UPDATE 568 1 8160 dbo smokey perl SELECT 595 10 1 dbo froth isql DELETE 646 1 0 guest walker isql SELECT 775 4 48723 joe_user mohindra acctng SELECT (7 rows affected)
Because sysprocesses is built dynamically to report current processes, repeated queries produce different results. Repeat this query throughout the day over a period of time to determine which applications are running on your system.
The CPU and physical I/O values are periodically flushed to the syslogins system table, where they increment the values shown by sp_reportstats.
After identifying the applications running on your system, use showplan and statistics io to evaluate the resource usage of the queries in the applications.
