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.
DISABLE_PARAMETER_SNIFFING Hint: Turn Off Sniffing for One Query
The DISABLE_PARAMETER_SNIFFING hint makes one query ignore the parameter value it was first compiled for. This post compares it with the default, OPTIMIZE FOR UNKNOWN and RECOMPILE on a table where one customer owns 40 percent of the rows. The reads show which variant fits which workload.
OPTION FAST N: First Rows Sooner, Total Reads Higher
OPTION FAST N tells SQL Server to plan for the first N rows, so the first rows arrive in milliseconds and the whole result costs much more. This post measures both sides on 300,000 rows with logical reads and time to the first row. It also explains why the hint belongs to paging and not to tuning.
Reasons for Slow Performance in SQL Server: The Top Five
The reasons for slow performance in SQL Server fall into five groups: schema design, T-SQL code, indexing, server deployment and hardware. This post tests three of them on a demo database, with logical reads as proof. It also shows a query that checks four deployment settings.