We’ve all been there: the SQL Server Database Administrator has been running slowly and everyone’s getting cranky. How can you tell what’s wrong and what steps you need to take to restore normal operations?
Database administrators have great instrumentation. When asked, they’re happy to tell you about their bottlenecks in excruciating detail. Use this handy guide to understand and troubleshoot the causes of the most common five wait types likely to impact your DBA’s performance.
This is a wait on memories that are being returned from long-term storage.
This wait frequently occurs when the DBA is trying to remember what happened that other time two years ago when queries for this application were returning incorrect results and the name of the developer who’s going to get stuck with the bug this time.
If this wait occurs frequently, the DBA needs to become harder to find.
This wait occurs when so many incidents are coming in that support tickets can’t be created fast enough to keep up. This wait is caused by overeager monitoring and ticketing systems that require clicking lots of buttons.
When this wait type dominates the DBA system, it can be resolved by hiring a Junior DBA for extra processing cycles.
Long queues of requests form when multiple users have complaints and form a line behind the DBA’s cubicle.
When LCK_USER waits become high, the DBA can clear them by yelling “Everyone run, I see the deadlock monitor!”
No explanation needed.
High CAFFEINEPACKET waits are a symptom of coffee starvation in the DBA. This can occur when too much work is being requested while caffeine sources have become low in the DBA.
To address this wait, quad shots of espresso may be taken in parallel, or may be spread across as many DBA sockets as are available.