Finding nested references
Your stored procedure calls another procedure, which calls a view. How deep does it go? Here's a recursive CTE that traces the full dependency chain, including cross-database references.
Debugging
16 posts
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.
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.
Misleading SSMS Connection
Read a cautionary tale on how I've deceived the SSMS, then promptly forgot about it and, in turn, ultimately confused myself.
Unexpected Blocking during the Indexed View Creation
Here are the chronicles of my struggles to add a large Clustered index to a View without causing an outage.
What's in a name? (T-SQL Tuesday #152)
My T-SQL Tuesday rant: an app that connects as '.Net SqlClient Data Provider' turns debugging into a manhunt. Please, name your apps.
What is causing my constraint to be untrusted?
You fix an untrusted constraint, and by morning it's back to untrusted. Here's how to catch the process responsible, using Extended Events.
SQL Server Module Signing for Secure Cross-Database Access
I keep relearning how to set up secure cross-database access, so here's the reminder: a diagram plus a full follow-along example you can run yourself.
Expecting Subvertations
How can 15 be less than 13? A perfect storm of bad assumptions about sql_variant comparison rules.
How to audit data modifications with surgical precision
A colleague needed to find who was changing a specific cell in a busy table. SQL Audit was too noisy, Query Store too vague. I found a way to combine triggers with Extended Events for surgical precision.
IS Lock in RCSI Enabled Database
I found an IS lock in a deadlock report on an RCSI database. That shouldn't be possible. Here's what caused it.
Don't Be Lazy
Sometimes the fastest help is the kind you find yourself. A rundown of the lazy question patterns I keep seeing in SQL help channels.
Discover potential obstacles in your design
One query against sys.messages can reveal every edge case SQL Server knows about before you commit to a new design.
Investigating Errors With Extended Events
Production errors are hard to reproduce. Here's my go-to Extended Events session that captures errors with their full TSQL calling stack.