SQL Server 2019 Configuration Manager provides an interface for instant file initialization. My lab also used it with SQL Server 2017.
Table Variable Deferred Compilation: Before and After SQL Server 2019
Compare table variable deferred compilation at compatibility levels 140 and 150, then decide when a temporary table gives better estimates.
Parameter Sniffing Fixes Compared: Which One to Use
Parameter sniffing fixes compared on one table show a clear order, from a cheap guess to a better index. This post runs the plain procedure, a local variable, OPTIMIZE FOR UNKNOWN, a scoped setting, OPTION (RECOMPILE) and a covering index on the same skewed table, and prints the page reads of each in both call orders.
Database Scoped Configuration: Turn Off Parameter Sniffing
A database scoped configuration lets you turn off parameter sniffing for one database with a single statement. This post measures a procedure with the setting on and off, shows that the change clears the cached plans of the database, and explains what the switch does not fix.
Lightweight Query Profiling: Live Progress Without the Old Overhead
Use lightweight query profiling to inspect live operator rows and in-flight plans, while avoiding misleading progress percentages.
NOCOUNT Performance: What SET NOCOUNT ON Saves in a Loop
NOCOUNT performance depends on the messages that SQL Server no longer sends to the client. This post counts the network packets of a procedure with a 5,000 step loop, with the setting off and on. It also explains why some tests show no time gain, and where the setting helps most.
Clone Database in SQL Server With DBCC CLONEDATABASE
Clone Database in SQL Server with DBCC CLONEDATABASE and you copy the structure and the statistics without a single row. This post runs the command on a demo database, checks what the clone holds and what it leaves out, and shows that the optimizer builds the same plan from the copied statistics.