How to
Deploy at Low Priority
Deploy SQL Server schema changes without a maintenance window using a WAIT_AT_LOW_PRIORITY-inspired loop that checks for SCH-S locks and bails out with GOTO.
Notes from a TSQL trench
Database stuff,
with a side of memes.
Long-form notes on TSQL performance, Extended Events, Query Store and the tooling around them. Written from the trenches, not the slide deck.
Featured
Opinion
Why is fn_xe_file_target_read_file the worst SQL function?
Why SQL Server's fn_xe_file_target_read_file is so frustrating: an nvarchar(max) return type, broken timestamp filtering, and no efficient way to find rollover files.
Tools
My Toolbox - SSMS
How I configure SQL Server Management Studio: window layout, the Options settings that matter, essential hotkeys, and small productivity wins for DBAs.
Deep Dive
Async stats update causing blocking
How lock partitioning causes async stats updates to block during online index rebuilds with WAIT_AT_LOW_PRIORITY - full repro and the SQL Server 2022 fix.
How to
Default event_file path for Extended Events
SQL Server stores event_file data in the error log folder when no full path is given. Steps to find the current location and change it, on Windows and in Docker.
Investigation
Unexpected Blocking during the Indexed View Creation
Creating the clustered index on an Indexed View blocked unrelated queries even under RCSI. The cause was Enterprise view matching, fixable with EXPAND VIEWS.
Investigation
What is causing my constraint to be untrusted?
A CHECK or FOREIGN KEY constraint keeps reverting to untrusted overnight. Here is how to catch the BULK INSERT or bcp process doing it, with Extended Events.
Deep Dive
Scary Scalar Functions - Part One: Parallelism
Scalar UDFs silently force your query to run serially, killing parallelism. Even a reference through a view, computed column, or check constraint does it.
How to
SQL Server Module Signing for Secure Cross-Database Access
How to set up module signing with certificates in SQL Server for cross-database access - without ownership chaining. Complete walkthrough with diagram and follow-along example.
Opinion
Why my way of writing SQL is superior
Aaron Bertrand has thrown a gauntlet on Twitter. Instead of commenting on how you do stuff differently, blog about it yourself. Well, challenge accepted!
How to
Query Hash and Query Plan Hash Mapping
DMVs and Query Store store the query hash as binary(8), but Extended Events shows a number, and the two refuse to match. Here's how to map between them.
Deep Dive
KEEP PLAN Demystified
The KEEP PLAN hint is supposed to relax recompilation thresholds, but the docs are vague. I test it against permanent and temp table statistics thresholds.
Investigation
How to audit data modifications with surgical precision
Combine an AFTER UPDATE trigger with Extended Events to surgically audit only the data changes you care about, cutting through thousands of modifications.
Investigation
IS Lock in RCSI Enabled Database
An IS lock appeared in a deadlock report on a database running RCSI - which shouldn't take shared locks. The culprit turned out to be an Indexed View.
Deep Dive
Query Store default settings
Query Store defaults aren't exactly what the docs say. This post finds the real values using Extended Events and explains what Auto capture mode actually means.
How to
Investigating Errors With Extended Events
Track down SQL Server errors using Extended Events. Capture the full calling stack, parse it with TSQL, and pinpoint exactly where failures happen.
Latest posts
Why is fn_xe_file_target_read_file the worst SQL function?
A rant about fn_xe_file_target_read_file - the nvarchar(max) return type, broken time filtering, and the circular dependency for finding rollover files.
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.
Database CI/CD Basics (T-SQL Tuesday #177)
A crash course on database CI/CD: source control as the source of truth, state-based vs migration-based versioning, and the many moving parts of automated database deployments.
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.
My Toolbox - SSMS
My SSMS setup guide: layout tweaks, essential Options settings, hotkeys I can't live without, and miscellaneous tips for a better SQL Server Management Studio experience.
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.