IO_COMPLETION wait stats measure queries waiting on disk work that isn’t a normal data page read, such as sorts and hashes that spill to tempdb. On Night 10 at the Clipboard Diner, a sheet cake for twelve turns out to be for sixty. Learn why spills happen, how to find them, and what SQL Server 2022 changed for log file growth.
PAGEIOLATCH Wait Stats: Waiting for Data From Disk
PAGEIOLATCH wait stats measure queries waiting while data pages are read from disk into memory. On Night 9 at the Clipboard Diner, the prep counter is too small for the menu and the cooks keep sending for things from the basement. Learn the two causes, how to read file latency per file, and the fixes in order.
SQL SERVER – Plan Cache and Data Cache in Memory
Two scripts I share with clients all the time: they show the plan cache and data cache in memory, with cached plans by size and cached pages by table.
SQL SERVER – Minimum Maximum Memory – Server Memory Options
While reading about server memory options, I learned the lowest value you can set for max server memory is 16 MB. Anything lower is reset to 16 MB.
Sizing a New SQL Server
Sizing a new SQL Server starts with workload evidence, growth, and recovery goals to estimate CPU, memory, storage, and headroom.
SQL SERVER – Queries Waiting for Memory Allocation to Execute
Find queries waiting for memory allocation with sys.dm_exec_query_memory_grants, plus wait_order and the query plan, when you suspect memory is short.
Finding Single-Use Plans Wasting Memory in the Cache
Find single-use plans, compare their cache memory with the total, and inspect parameterization before discussing server-wide cache settings.