Identify oversized data files with FILEPROPERTY, separate internal free space from disk capacity, and review one-time shrink choices.
VLF Growth Rules: How Many Virtual Log Files One Growth Adds
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.
Turning Per-Second Counters Into Real Rates From DMVs
That per-second counter on your dashboard is probably a running total. Here is how to turn it into a real rate with two samples.
TRY_CAST vs CAST: Finding Rows That Will Not Convert Before a Load
Find text values that will not convert before loading, separate invalid and missing input, and use explicit date styles and culture parsing.