Analyzing for slow performance is difficult, as you know, because the issue could be in the server, on the workstation, in the application, inside the database engine, or due to hardware issues -- or any misconfiguration of the above!
Here are the obvious things that have been known to cause performance issues in the past:
- Overloaded servers
- Overloaded workstations
- Operating system low on memory
- Operating system swapping the database memory to disk due to aggressive caching
- Database configured with too few resources (Need to review L1 cache, L2 cache, OS cache, XIO Cache, (I/O Threads, CommThreads, etc.)
- Hard disk RAID5 array with a failed drive
- SCSI bus problems (like termination)
- network configuration issues on the server
- network configuration issues on the workstation
- network configuration issues somewhere between the server and workstation
- applications searching for nonexistent resources and pausing at the client side
- applications issuing complicated SQL queries without sufficient indices for optimization
- applications issuing Btrieve GetNextExtended calls with lots of record accesses
- applications reading data files that have been cleaned out by a mass purge, but not rebuilt
- bad network cabling causing excessive retransmissions
- large Ethernet collision domains
- large Ethernet broadcast domains with an ensuing broadcast or multi-cast storm
- Users unattainable performance requirements
- virtualization
- ...and more...
Many of these issues can be identified with simple tools, including Task Manager, Performance Monitor, Wireshark, Process Monitor, and others, but some issues may require a dive down into the hardware (i.e. the SCSI issues).
If you are not able to find a solution on your own, then you may wish to consider some outside help. See our web page at www.goldstarsoftware.com/perfopt.asp for more information on this process.