Once you have identified the peak period, you can look for the bottleneck. Examining ResUsage data is one technique helpful in finding bottlenecks.
Some possible bottlenecks are:
- CPU saturation
- Disk saturation
- Free and/or FSG cache memory
- BYNET
- Vprocs (hot AMPs, coexistence, load balancing)
- Channel (number of sessions or channel speed)
- LAN/gateway (number of sessions or network connections)
- Lock contention
Resource Usage by Workload
ResSpsView, a view of the ResUsageSps table, allows you to see the percentage of CPU used by different workloads, determine which workload is responsible for I/O skew, and examine queue wait time and service time numbers to find backed up queries.
For ResUsageSps table column definitions, see Resource Usage Macros and Tables.