The VLF growth rules say how many virtual log files one log growth adds: one up to 64 MB, eight up to 1 GB, sixteen above. This post measures them on a demo database, shows a log with 68 VLFs from 1 MB steps, and repairs it by shrinking and growing once.
Log File Growth and Instant File Initialization: The 64 MB Rule
The 64 MB rule says log file growth can skip zeroing when instant file initialization is on, but only up to 64 MB. Two trace flags make the SQL Server error log show a 64 MB growth skipping the zeroing and a 65 MB growth zeroing in full. The last section says what to change.
statement_start_offset: Showing the Exact Statement Running Now
A stored procedure runs for forty minutes and you need the busy line. Here is how to pull it out of the batch text without the usual off-by-two mistakes.
SQL Server: How to Display Row Numbers in Query Editor for Efficient Query Editing
Let us learn how to Display Row Numbers in Query Editor for Efficient Query Editing. Comprehensive Database Performance Health Check
Performance Tuning Mistakes in SQL Server: 11 Habits to Fix
Performance tuning mistakes are habits, and each one has a check. Eleven sit in one table, and five are measured on a demo database: a missing index, stale statistics, a row by row loop, a giant delete and a type that cannot be indexed.
Updatable Ledger Tables: Proving No One Quietly Changed a Row
Update a row in a ledger table and the old version stays. Then verify the history with a digest, and break the digest to see what failure looks like.
Cold Cache vs Warm Cache: Testing Query Speed Fairly
Compare cold cache and warm cache query tests using isolated buffer clearing, logical and physical reads, and controlled measurements.