AUTO_UPDATE_STATISTICS_ASYNC is the setting that cleared a new wait type, WAIT_ON_SYNC_STATISTICS_REFRESH, for my client. With auto update statistics on and stale statistics, the query halts while the statistics update. Switching the update to asynchronous removed the wait.
SQL SERVER – Temp Table vs Table Variable – Cardinality Estimation
I had a very interesting conversation with one of my clients about Cardinality Estimation for Temp Table vs Table Variable. Let us discuss it today.
Why a Filtered Index Is Ignored by Parameterized Queries
Explain why a filtered index is ignored with parameters, inspect plan warnings, and compare literal filters, RECOMPILE, and coverage fixes.
SQL SERVER 2019 – How to Turn On or Enable Instant File Initialization?
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.







