Service Broker Blocking
I was paged for a blocking chain where the lead blocker had a transaction name I'd never seen: CSbRollbackHandlerTask::DisableQ. Here is how I reproduced it and why the blocked process report refuses to help.
Deep Dive
10 posts
Async stats update causing blocking
An index rebuild quietly blocked an async stats update, and the whole server started timing out. Here is the lock-partitioning repro and the SQL Server 2022 fix.
Are ABORT_AFTER_WAIT's victims logged?
Does ALTER INDEX REBUILD with WAIT_AT_LOW_PRIORITY log its victims in the error log the same way KILL does? Let's find out.
Scary Scalar Functions - Part Four: Your Environment
You've learned why Scalar UDFs are slow and how to remove them. The last question: where do you even start? Here's how I triage them across a whole environment.
Scary Scalar Functions - Part Three: The Cure
Nuke them from orbit, or inline them. The practical ways to neutralize Scalar UDF performance pain, from a free SCHEMABINDING win to a full ITVF rewrite.
Scary Scalar Functions - Part Two: Performance
A Scalar function that does literally nothing still runs over 20 times slower. Here is the proof, measured four different ways.
Scary Scalar Functions - Part One: Parallelism
Consultants hate me for this one weird trick. Because I'll be uncovering all the mess Scalar functions can cause in a series of articles.
New Extended Events in SQL Server 2022
SQL Server 2022's CTP has dropped. I diffed its Extended Events against 2019 and pulled out the few new ones worth a look.
KEEP PLAN Demystified
Ever wondered what the KEEP PLAN hint actually does? The docs are vague, so I tested it against every recompilation threshold to find out.
Query Store default settings
Query Store comes with default settings - but are they what the documentation says? Let's find out using a simple Extended Events session.