Troubleshooting the Top Five Waits Occurring in SQL Server DBAs

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.

5: BRAINIOLATCH_SH

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.

4: TICKETLOG

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.

3: LCK_USER_X

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!”

2: REDDIT

No explanation needed.

1: CAFFEINEPACKET

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.

Previous Post
Why Your SQL Server Cluster Shouldn’t Be Virtualized
Next Post
Congratulations, You’re an MVP! Here’s What You Need to Know.

12 Comments. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}