What’re we waitin’ fer?!?!?
Had to channel my inner Mickey for the title of the post…Rocky II is one of the first movies that I remembering seeing as a re-run on cable…but I digress.
When I read Robert’s post and subject for this month’s T-SQL Tuesday, my mind immediately went to one of my first experiences with wait stats, as it was a very poignant “ah-HAH!” moment for me. I was an administrator for several SQL Server instances and had been doing it for a while, but knew very little about SQL Server’s wait stats. A few nights before my incident I was reading through Jonathan Kehayias’ (B|T) “Troubleshooting SQL Server: A Guide for the Accidental DBA” which has a section devoted to finding and diagnosing issues around waits. I had saved the supplied queries and was developing a method for saving wait information and analyzing it over time, to help with my administration.
So there I was, at work taking care of whatever that was the radar that day. Cue my users: emails and phone calls began coming in, telling me that our campus imaging database was unresponsive and that the application was down. Our monitoring software wasn’t reporting any unusual resource strains on the instance and the other databases weren’t having any problems. Enter my shiny new query: I ran it and found a wait on BACKUPTHREAD. It turned out that my hourly backup of the imaging database had hung up…using the very same query I found that I wasn’t having any problems with I/O. I was unable to kill the backup process and a phone call to Microsoft had me restart the instance.
It was awesome to be able to use something that was pretty fresh in my mind and be able to get to the root of an issue in just a few minutes. I remember thinking “heck, that was pretty badass.”
Thanks to Robert Davis (B|T) for this month’s topic.
Here’s some more information about T-SQL Tuesdays (#tsql2sday on Twitter)
Job well done, Dan. Thanks for joining in on T-SQL Tuesday #49.
Pingback: T-SQL Tuesday #49: Wait For It Roundup | SQLSoldier