4. Blocking Hell: When Queries Collide

       It was 10:00 AM on a Wednesday, and Sam’s desk already looked like a command center: multiple monitors glowing, Post-its everywhere, and a half-finished coffee that was more caffeine than liquid.

That’s when the first alarm bell rang — the blocking report from the monitoring tool lit up bright red. Sam’s inbox pinged seconds later:

“The app is hanging! Users are stuck! Is it the database?”

Sam grinned. It was time to dive in.

He opened SSMS and ran his favorite script, to check any long running or blocking SPID. 

And there it was — the blocking chain in all its messy glory. Like a conga line of stuck queries, each politely waiting for the one in front to move. Except the head blocker wasn’t in a hurry at all.

Priya sipped her tea. “If it’s a report, why is it running now? Did someone trigger it by accident?”

The junior DBAs — Asha, Raj, Neha — were taking notes, their eyes wide.

Sam knew better than to assume. Before killing the head blocker or tweaking the query, he pinged the App Team in the group chat.

“Hey team — we’re seeing a big reporting query running right now, and it’s causing some serious blocking. Was this intentional?”

A moment later, Olivia from the App Team replied:

“Oh wow — that’s supposed to be a weekly report for off-hours only! Must have been triggered by accident.”

Sam grinned. That’s all he needed to know.

He quickly confirmed no one needed the report at that moment, and politely killed the lead blocking session:

The blocking chain melted away like ice in the sun. Users were moving again, queries were flying, and the whole system felt lighter.

The Lesson Learned

Sam took a sip of his cold coffee and said to the team:

“Before killing anything or adding indexes, always check with the app team. If it’s not needed, kill the blocker. If it’s needed, we fix it properly.”

He added:

•  Big reports should never run during peak hours.
•  Communication with the app team saves time and stress.
•  And sometimes, the best index is the one you don’t need to create at all!

Technical Tips

Here’s what Sam used to crack the case:

• sys.dm_exec_requests – Shows who’s waiting and who’s causing the block.
• sys.dm_exec_sessions – Tracks open transactions and running sessions.
• sp_whoisactive – Handy for visualizing the blocking chain.
• KILL <session_id> – A last-resort but powerful tool.
• Communication – Your best friend when solving blocking mysteries!

With the blocking chain gone, the office buzzed back to normal. Sam leaned back in his chair, took another sip of his coffee, and thought:

“Blocking hell averted — thanks to a quick check with the app team. What’s next?”

Because in the world of databases, there’s always another mystery — and another day...