Query Specific Wait Statistics helped explain a sudden customer-query slowdown. The remaining workload ran normally, and recompilation had not helped.
Disable Resource Governor: Why RECONFIGURE Turns It On
To disable Resource Governor, run ALTER RESOURCE GOVERNOR DISABLE. The post shows why RECONFIGURE enables the feature, with a tested script that restores the state it finds. It also explains how saved pool limits come back to life.
SCHEMA_ONLY vs SCHEMA_AND_DATA for Memory-Optimized Tables
Compare SCHEMA_ONLY and SCHEMA_AND_DATA memory-optimized tables, inspect logging and recovery, and test an isolated offline-online cycle.
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.
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.
CHECKPOINT Duration: Can You Speed Up a Manual CHECKPOINT?
The CHECKPOINT duration argument sets a target time in seconds. This post times a manual checkpoint on a database with 130 MB of dirty pages, and shows that a long duration slows it badly while a short one does not make it faster. It also shows that a backup flushes dirty pages by itself.