You can run sp_sysmon before and after tuning Adaptive Server configuration parameters to gather data for comparison. This data gives you a basis for performance tuning and lets you observe the results of configuration changes.
Use sp_sysmon when the system exhibits behavior you want to investigate. For example, to find out how the system behaves under typically loaded conditions, run sp_sysmon when conditions are normal and typically loaded. For example, consider whether it makes sense to run sp_sysmon for 10 minutes starting at 7:00 p.m., which is before batch jobs begin and after most of the day’s OLTP users have left the site. Instead, run sp_sysmon during the normal OLTP load and during batch jobs.
In many tests, it is best to start the applications first, and start sp_sysmon when the caches are likely to have reached a steady state. If you are trying to measure capacity, be sure that the amount of work you give the server keeps it busy for the duration of the test.
Many of the statistics, especially those that measure data per second, can look extremely low if the server is idle during part of the sample interval.
In general, sp_sysmon produces valuable information when you use it:
Before and after cache or pool configuration changes
Before and after any sp_configure changes that may effect performance (for example, changes to memory sizes, caches, or disk I/O related options)
Before and after the addition of new queries to your application mix
Before and after an increase or decrease in the number of Adaptive Server engines
When adding new disk devices and assigning objects
During peak periods, to look for contention or bottlenecks
During stress tests, to evaluate an Adaptive Server configuration for a maximum expected application load
When performance seems slow or behaves abnormally
You may also find sp_sysmon to be helpful during query or application development. For example, when you are:
Working with indexes and updates to see if certain updates reported as deferred_varcol are resulting in direct versus deferred updates
Checking the caching behavior of particular queries or a mix of queries
Tuning the parameters and cache configuration for parallel index creation