Myth: “Always rebuild all indexes weekly, regardless of fragmentation.”

Truth:

Measure fragmentation first (sys.dm_db_index_physical_stats). Below 5%, do nothing. A tiny, rarely-scanned table at 60% fragmentation often doesn’t matter at all. Blind scheduled rebuilds waste CPU/IO on indexes that never needed it.

Myth: “More RAM always fixes a slow query.”

Truth:

More RAM only helps if the bottleneck is actually buffer pool pressure (pages being evicted and re-read from disk). It does nothing for a CPU-bound query, a lock-bound query, or a query with a genuinely bad plan from a missing index.

Myth: “A high cost % operator in the execution plan is always the real bottleneck.”

Truth:

Cost % is computed from row estimates, which can be badly wrong under parameter sniffing or stale statistics. Cross-check against actual row counts — the Estimated-vs-Actual gap is often more informative than cost % alone.

Myth: “Adding an index can only help, never hurt.”

Truth:

Every index adds write-maintenance cost to every INSERT/UPDATE/DELETE that touches it. An index that helps a rarely-run report but slows down a high-throughput write path is a net loss — always weigh read benefit against write cost.

Myth: “PAGELATCH waits are the same problem as lock (LCK) waits, and the same fix (shorten transactions) applies.”

Truth:

Latches protect physical memory structures; locks protect logical transaction consistency. Applying lock-blocking fixes to a latch contention problem (like tempdb allocation contention) solves nothing — the fix is structurally different (e.g. more tempdb files, not shorter transactions).

Myth: “WITH (NOLOCK) on every query is a safe, free performance win.”

Truth:

NOLOCK (READ UNCOMMITTED) permits dirty reads — data that may later be rolled back, or in rare cases, skipped/duplicated rows during concurrent page splits. It’s a genuine tool for tolerant reporting scenarios, not a default habit to apply blindly everywhere.

Myth: “If a query is slow, the database server needs more powerful hardware.”

Truth:

The 13-Hour Delete case study earlier in this course was fixed with one targeted index, not a hardware upgrade. Diagnose with evidence first — hardware is sometimes genuinely the answer, but it’s usually the most expensive way to mask an unindexed query.