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.
DISABLE_OPTIMIZER_ROWGOAL: How a Row Goal Changes the Plan
DISABLE_OPTIMIZER_ROWGOAL switches off a row goal, the optimizer’s bet that a query needs only a few rows, and TOP, FAST, IN and EXISTS all place that bet. This post reads the estimates behind it, shows how the plan shape changes, and shows when the bet fails. A covering index fixes the failure, and the hint does not.
SQL SERVER – Heaps, Scans and RID Lookup
The question was do heaps are very bad and always force a table scan? Let us learn about RID Lookup, Heaps and table scan.
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.
What is TempDB Spill in SQL Server? – Interview Question of the Week #259
When SQL Server uses TempDB when any query does not have enough memory to do its operation, it is called TempDB Spill. Let’s learn today.
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.