Identify a trivial plan in SSMS and cached XML, understand skipped optimizer work, and compare a more complex query without forcing it.
What is Faster, SUM or COUNT? – Interview Question of the Week #231
Question: What is Faster, SUM or COUNT? Answer: Both are the same. I was asked this question during my Comprehensive Database Performance Health Check.
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.
TempDB Performance: Five Settings to Check in SQL Server
TempDB performance depends on five settings that you can read in a few minutes: how big it is, how many data files it has, whether they match, how it grows and where it lives. Three read-only queries check each one. The post also explains what to do with the answers.
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.
CHECKPOINT vs DBCC DROPCLEANBUFFERS: Impact on Memory
CHECKPOINT vs DBCC DROPCLEANBUFFERS comes down to which pages leave memory. CHECKPOINT writes dirty pages to disk, and DBCC DROPCLEANBUFFERS removes clean pages from the buffer pool. Here is the effect on memory, with results.
Dirty Pages and Clean Pages in the SQL Server Buffer Pool
Dirty pages are pages in memory that SQL Server has changed but not yet written to the data file. This post counts dirty pages and clean pages per database and per object, then watches a CHECKPOINT bring the dirty count to zero. It also explains why you should not try to clean the buffer pool on a production server.