DMVs to the Rescue
Recently I had a database server performance drop dramatically. Query times were three to four times longer than previously with no apparent cause. It was as if someone flipped a switch, and everything starting running really slowly. This is on a windows machine running Microsoft SQL Server.
When this kind of thing happens there can be any number of causes. It could be internal in the form of competing queries or out of data indexes or statistics. It could be hardware related in that you have grown to utilize more memory and are now swapping, or your CPU utilization has increased. It could be external in the form of network resources such as SAN connections, or simply network connections. It could be simply a saturation of your disk medium causing your queries to wait, but not show up as blocked.
All of these kinds of things may cause your CPU utilization to skyrocket. Shortage of memory, long disk queue lengths, network backlogs, long waits for writing cache to disk, etc.
How do you determine what is the cause of your issues? That is where the DMVs come in. They can help you detect the underlying cause or causes resulting in your reduced performance. Let me recommend once again a wonderful book by Ian W. Stirk called “SQL Server DMVs in Action”. This is a must have book for any serious Microsoft SQL Server DBA. It provide information not only fighting server performance problems, but also covers simplifying day to day tasks with Dynamic Management Views (DMVs).
Well, I have to go back to putting the DMVs to work, and sort out my performance issue. Leave your comments with your favorite tip for isolating performance bottlenecks.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)
Featured Script
script to return non-SA owners of objects
grabs the dbo owner field from master for each DB; then for each db looks for objects owned by non-SA…. (read more)