Backup & Recovery from First Principles, Part 9: BUFFERCOUNT, MAXTRANSFERSIZE, and Friends – Benchmarked
The BACKUP statement has a set of tuning knobs most people never touch: BUFFERCOUNT, MAXTRANSFERSIZE, BLOCKSIZE, and striping across multiple files.[1] Advice about them tends to be folklore, so this post is numbers instead: nine…
Scalar UDFs vs Inline TVFs: Measuring the Damage
Scalar user-defined functions are the most natural thing in the world to write. You have a bit of logic, you wrap it in a function, you call it in your SELECT list, and your query…
Finding Eager Index Spools in the Plan Cache
Somewhere in your plan cache right now, there is probably a query quietly building an index every single time it runs. Not once. Every execution. It builds the index, uses it, and throws it away,…
Catching Slow File I/O with a Filtered Extended Events Session
file_read_completed and file_write_completed fire on every I/O. Filter on duration to capture only the slow ones, then shred to name the file.
Reads Are Part of the Contract: A DBA’s Guide to ORM-Generated SQL
In I argued that the database is a published interface, and that the way to keep it changeable is to draw a boundary: a small, deliberate set of views and procedures is the contract, and…
Upgrading Database Compatibility Levels Across a Whole Instance
The instance upgrade went perfectly. You moved from SQL Server 2019 to 2022, the installer turned green, the services came back up, and the application connected on the first try. A week later someone asks…