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 flushed to the syslogins system table periodically 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.
If you have configured Adaptive Server to enable resource limits, you can use showplan to evaluate resources used prior to execution and statistics io to evaluate resources used during execution. For information on configuring Adaptive Server to enable resource limits, see “Enabling resource limits”.
In addition to statistics io, statistics time is also useful for evaluating the resources a query consumes. Use statistics time to display the time it takes to execute each step of the query. For more information, see “Diagnostic Tools for Query Optimization” on page 12-6 in the Performance and Tuning Guide.
