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.
Michael J Swart October 1, 2012 | 8:20 am
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.
Kendra Little October 2, 2012 | 3:58 pm
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!
Peter Chenoweth October 1, 2012 | 8:41 am
Exactly what I needed on a Monday morning. Thanks!
Ayman El-Ghazali October 1, 2012 | 10:08 am
I wonder if a KICK ALL command could be used for LCK_USER_X
Nice Monday post
Jason October 1, 2012 | 10:21 am
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
gserdijn October 1, 2012 | 2:03 pm
Very nice post.
EYEOPEN_COMPLETION – Used to indicate a DBA waiting for eyes to open. Typical Monday Wait. Requires instant use of matchsticks.
Tony October 2, 2012 | 1:14 am
I’m always proactive in avoiding CAFFEINEPACKET. I use a solution by Starbucks.
Kendra Little October 2, 2012 | 3:58 pm
Third party services for this wait are particularly nice in the afternoon. I could use one now.
mst October 2, 2012 | 3:07 pm
Reminds me of the old IBM Assembler opcode I was told about (a long time ago…):
HCF: Halt and Catch Fire
Kendra Little October 2, 2012 | 3:57 pm
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.
David Hay October 3, 2012 | 12:55 pm
Love it! It is now posted right above my coffee maker.
Links October 7, 2012 | 10:37 am
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!