One of the most popular conversations during Comprehensive Database Performance Health Check is about CPU and memory issues.
Cached Data Per Object in Memory: Read the Buffer Pool
Cached data per object tells you which tables and indexes fill the buffer pool. This post builds two demo tables, empties their cache and reads part of the data. A query then lists the pages, megabytes and share of each index in memory. It also explains why the view is costly on a big server.
Find Expensive Queries – SQL in Sixty Seconds #159
Earlier I wrote a blog post about SQL SERVER – List Expensive Queries – Updated March 2021. Let us learn how to Find Expensive Queries.
CHECKPOINT Covers One Database: Flush Data to Disk
CHECKPOINT covers one database, so to flush data to disk you run it in each database. A demo with two databases shows one flushed and one still dirty. A short script then flushes every database in one pass, and the post says when a flush is worth it.
Expensive Queries by query_hash: Total Cost, Not One Plan
Expensive queries by query_hash are ranked by total cost, not by single plans. A demo runs one lookup with 40 different literals and shows that the top single plan is a scan, while the grouped total, ranked by reads, points at the lookup. The query ranks by reads, CPU, time or runs.
Memory-Optimized Tables as a Key-Value Cache
Build a key-value cache with SCHEMA_ONLY memory-optimized tables, native get and set procedures, expiry checks, and bounded cleanup.
RESOURCE_SEMAPHORE vs THREADPOOL: Two Waits, Two Fixes
RESOURCE_SEMAPHORE vs THREADPOOL tells you whether a task waits for memory or for a worker thread. This post reads both counters and checks for live waits. A demo shows how an index shrinks a memory grant from 38,216 KB to zero.