Capturing and resolving blocking chains, deadlocks, and application locks.
40 associated posts39 primary posts
There's a secret theme to the questions I hand-picked from the queue at https://pollgab.com/room/brento. Normally I just take the top-voted ones, but today, see if you can spot the thing they all have in common:
https://www.youtube.com/watch?v=reaEWQ1TzLg
I'm kinda weird. I get excited when I'm troubleshooting a SQL Server problem, and I keep hitting walls.
I'll give you an example. A client came to me because they were struggling with sporadic performance problems in Azure SQL DB, and nothing seemed to make sense:
For this month's T-SQL Tuesday, I asked y'all to write about the most recent ticket or issue that you closed. (If you want to see other peoples' posts, you can check out the comments on that invite post, or wait til next week and I'll publish a wrap-up of everyone's answers.)
A past client emailed me with a performance emergency. Things had been going just fine, and then out of nowhere, things suddenly slowed down.
After a long trip through Scandinavia, I'm back in the desert. You posted questions at https://pollgab.com/room/brento and I took the hot seat:
https://youtu.be/guzQGYx3R2M
Before heading out to Old Town for sightseeing, I went through your top-voted questions from https://pollgab.com/room/brento.
https://www.youtube.com/watch?v=5x1SwTkBpFk
Got a question for me? Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me discuss. Before heading to NYC for vacation, I recorded a live Office Hours on my Twitch channel.
https://youtu.be/iuv-HOqlKBw
You're investigating your SQL Server's top wait stats, and you're noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you're just not getting complaints from your end users, and you're wondering what's causing it.
It might be overzealous index rebuilds.
Hey, this seems harmless.
Seriously, don't SET DEADLOCK_PRIORITY HIGH.
Think fast! Not all of the questions at https://pollgab.com/room/brento have long-winded answers. In this 5-minute session, I crank through a bunch of straightforward questions:
https://www.youtube.com/watch?v=cxq1P3mpxaY
You upvoted questions at https://pollgab.com/room/brento, and today I'm at the harbor in Heimaey to cover 'em.
https://www.youtube.com/watch?v=dbpo9aRWxAE
You keep getting warnings and emails about deadlocks, but let's be honest: you're not really sure how they happen or what to do about it. In this one-hour session from the SQLBits conference, I will show one, use sp_BlitzLock to analyze it, then fix it.
https://www.youtube.com/watch?v=3EwDn9hqgkg
No, not NOLOCK. You get the wrong query results, even when your query results aren't supposed to be changing.
I'm talking about when you need to do writes, but you want your query to get along with others in high concurrency environments, without creating a blocking firestorm.
When I'm troubleshooting a blocking emergency, the culprit is usually the query at the head of a blocking chain. Somebody did something ill-advised like starting a transaction and then locking a whole bunch of tables.
But sometimes, the lead blocker isn't the real problem. It's query #2.
When In-Memory OLTP first came out, Microsoft liked to go around bragging at conferences that "Nothing stops a Hekaton transaction." I remember hearing that and kinda chuckling at the time, and I was reminded of it by a recent client who was running into blocking issues with...yep, Hekaton.
To reproduce it:
When I first got started working with SQL Server, I assumed it was flawless because it came in a sealed box from a ginormous corporation. I figured they'd tested every edge case, and that there was no way things were gonna go sideways. These days, even though I read CU KB articles and giggle about…
Locking isn't a problem - queries are gonna lock data. As long as nobody else wants the lock, you can take your sweet time locking it, and it won't show up as a problem. (That's one of the many reasons queries work so well in development, and then hit performance issues in production.) Blocking, now…
In SQL Server Management Studio, plain ol' deadlock graphs look like this:
BOOOOO-RING. That's why I prefer opening deadlock graphs in SentryOne Plan Explorer, which presents a much better visual in the form of a circle...a circle, let's just stop there:
When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic's excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it's way more powerful. The documentation is extensive -…
Legendreary
In the last post, I looked at some issues with implementing foreign keys with cascading actions. Namely that, well, it fell apart pretty quickly just trying to set up.
I didn't even get to the point where I wanted to test all those relationships.
Worse Than Mobile Browsing
I have a love/hate relationship with Extended Events. Yes, they're powerful. Yes, you can track interesting things.
But they're just not intuitive, like, 10 years on.
