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.
12 Comments. Leave new
Quite excellent Kendra.
I have a solution for 2. Reddit: I put these entries into my hosts file:
127.0.0.1 facebook.com
127.0.0.1 http://www.facebook.com
127.0.0.1 reddit.com
127.0.0.1 http://www.reddit.com
(there’s no place like 127.0.0.1)
By the way, if you use Windows, you have to tell Windows Defender that the change isn’t because of malware. (Google “windows defender modifies facebook in etc hosts”
By the way 2: Malware, sounds like smuggled goods on Serenity.
Hosts files will never die. I seem to run into host file uses all the times– half the time it’s for pranks, half the time it’s duct tape for production!
Exactly what I needed on a Monday morning. Thanks!
I wonder if a KICK ALL command could be used for LCK_USER_X
🙂 Nice Monday post
Kendra, great work, I needed a laugh this Monday morning!
Michael, I’ve received your wave.
Ayman, my “Kick All” command only requires a nearby Tavern with free WIFI 🙂
Very nice post. 🙂
EYEOPEN_COMPLETION – Used to indicate a DBA waiting for eyes to open. Typical Monday Wait. Requires instant use of matchsticks.
I’m always proactive in avoiding CAFFEINEPACKET. I use a solution by Starbucks.
Third party services for this wait are particularly nice in the afternoon. I could use one now.
Reminds me of the old IBM Assembler opcode I was told about (a long time ago…):
HCF: Halt and Catch Fire
LOL– that’s actually documented in Wikipedia! I love the summary of the article:
Halt and Catch Fire, known by the mnemonic HCF, refers to several computer machine code instructions that cause the CPU to cease meaningful operation. The expression “catch fire” is intended as a joke; the CPU does not usually catch fire. (http://en.wikipedia.org/wiki/Halt_and_Catch_Fire)
Not USUSALLY. 🙂 But there’s always a chance.
Love it! It is now posted right above my coffee maker.
What about the old SQL_XP wait type?
Wait caused by waiting on SQL Cross Platform resources before task can be completed… usually seen in hybrid data store systems!