To list active and inactive VLFs for every database, count the rows of sys.dm_db_log_info by vlf_active and read the log reuse wait next to them. This post builds a demo log with 24 VLFs, shows how an open transaction and small autogrowth steps add more, and fixes the count with one shrink and one regrow.
KEEP PLAN Hint: Cut Temp Table Recompiles in SQL Server
The KEEP PLAN hint gives a temp table the same recompile threshold as a permanent table. This post counts statistics recompiles with Extended Events for a plain query, a KEEP PLAN query and a KEEPFIXED PLAN query. It shows where the hint helps, where it does nothing and what the stronger hint costs.
Proving a Tuning Change Worked With Before and After Numbers
Prove a tuning change with before and after numbers from Query Store for CPU, reads, duration, and comparable execution volume.
Remove Cached Plans Not Used for a Set Time in SQL Server
To clear cached plans not used for a set time, list the plans by their last execution time or by the time they were built, then clear each one by its plan handle. This post builds a small procedure that previews the plans first and removes them only when you switch it on. It also explains why the plan handle needs its 0x prefix.
AUTOGROW_ALL_FILES: What Replaced Trace Flags 1117 and 1118
AUTOGROW_ALL_FILES and MIXED_PAGE_ALLOCATION replaced trace flags 1117 and 1118, which once made tempdb grow its files together and use whole extents. This post checks the flags on a server, reads the settings that replaced them and demonstrates both on a demo database. It also says what to do with a startup parameter that still names them.
SQL SERVER – Stream Aggregate and Hash Aggregate
Recently during the on-demand consultation, I was asked a very interesting question about Stream Aggregate and Hash Aggregate.
Index Design Rules: Ten Don’ts and Where They Break
These index design rules came from a client’s list of ten index don’ts, and readers argued with several of them. This post turns the checkable rules into read-only queries on a demo database, and it measures what each rule really costs. The rest are warnings, not laws, and the post says which is which.







