Proving the Restore, Part 5: Striped Backups Are All or Nothing
Large databases are often backed up across several files at once. Instead of one 800 GB file, you get eight files of about 100 GB, written in parallel:
|
1 2 3 4 5 6 |
BACKUP DATABASE [Sales] TO DISK = N'D:\Backups\Sales_FULL_S00.bak' , DISK = N'D:\Backups\Sales_FULL_S01.bak' , DISK = N'D:\Backups\Sales_FULL_S02.bak' , DISK = N'D:\Backups\Sales_FULL_S03.bak' WITH CHECKSUM, COMPRESSION; |
The motivation is throughput. Several writers…
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…