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.
Dynamics SQL Server Settings: Five Checks for NAV, AX and CRM
Dynamics SQL Server settings decide most of the speed you get from NAV, AX and CRM, because you rarely control the application code. Five read-only checks cover tempdb, autogrowth, MAXDOP, automatic statistics and statistics age. Each one comes with a short query and a clear rule.
TempDB Metadata Tables: Check, Enable and Count Them
The tempdb metadata tables can live in memory, and four short steps tell you whether yours do. This post checks the setting, shows how to turn it on and off, and counts the tables SQL Server moves. It also says when the feature is worth a restart.
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 – Resolve WAIT_ON_SYNC_STATISTICS_REFRESH Wait Type
During Comprehensive Database Performance Health Check I recently faced a brand new wait type WAIT_ON_SYNC_STATISTICS_REFRESH.
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.