Query Specific Wait Statistics helped explain a sudden customer-query slowdown. The remaining workload ran normally, and recompilation had not helped.
Unique Index Performance: What a UNIQUE Index Saves
Unique index performance gains come from what the optimizer learns, not from smaller pages. A tested demo compares pages, reads, plans and CPU for a plain index and a unique index. It also shows the error a duplicate causes.
DBCC DROPCLEANBUFFERS Impact on Memory: See a Cold Cache in Action
DBCC DROPCLEANBUFFERS removes every clean page from memory, so the next query reads from disk. This post counts the pages of one database before and after, shows why a CHECKPOINT comes first, and compares a cold read with a warm read.
CHECKPOINT Impact on Memory: Watch Dirty Pages Go to Zero
CHECKPOINT impact on memory is easy to measure. This post counts the dirty and clean pages of one database before and after a checkpoint. Dirty pages drop to zero, the clean pages grow by the same amount, and nothing leaves memory.
Trivial Plan: Why Some Queries Skip Full Optimization
Identify a trivial plan in SSMS and cached XML, understand skipped optimizer work, and compare a more complex query without forcing it.
PAGELATCH_UP Waits and Suspended Sessions: Find the Hot Page
PAGELATCH_UP waits mean that sessions queue for the same page in memory, and that page can be named. This post reads the waiting sessions, turns the resource into a database, a file and a page, and tells an allocation page from an ordinary one. It then lists the fixes for tempdb and for a user database.
Removing a Bad Plan From Cache Without Clearing Everything
Remove one bad plan from cache by handle, compare narrower recompilation choices, and fix the cause so the plan stays healthy.