Catch Memory Grant Waits via sys.dm_exec_query_memory_grants
Oct 17, 2026 / · 10 min read · sql server sys.dm_exec_query_memory_grants sys.dm_exec_query_resource_semaphores sys.dm_exec_requests sys.dm_exec_sql_text sys.dm_os_memory_clerks sys.dm_exec_query_plan sys.dm_os_wait_stats sys.messages sys.dm_os_sys_info memory grants resource semaphore performance tuning scripts ·Finding the one query stuck behind a RESOURCE_SEMAPHORE wait starts with a plain fact most dashboards gloss over: a query waiting on a memory grant isn't failing yet — it's queued, holding a worker thread while SQL Server decides who gets the next slice of execution memory. If that wait outlasts the query's own timeout …
Read MoreA DMV query that grinds to five or ten minutes against a large plan cache is a real failure mode, not a hypothetical one — joining sys.dm_exec_query_stats to sys.dm_exec_sql_text row by row scales badly once the cache holds enough plans. sys.dm_exec_procedure_stats exists to sidestep exactly that cost: one row per …
Read MoreFind Ad Hoc Plan Cache Bloat with sys.dm_exec_cached_plans
Not every plan in SQL Server's cache earns its place. A query compiled once — because its literal values were hard-coded into the text instead of passed as parameters — gets a full compiled plan cached right alongside plans an application reuses thousands of times a day. sys.dm_exec_cached_plans is where that …
Read MoreRank Expensive Queries with sys.query_store_runtime_stats
Query Store buckets runtime statistics into fixed time intervals, which means a query that ran steadily all week doesn't produce one row of history — it produces one row per interval, split further by execution outcome. Averaging those rows unweighted is how a "most expensive queries" report quietly gets the ranking …
Read MoreRank Top Memory Consumers with sys.dm_os_memory_clerks
Jul 20, 2026 / · 8 min read · sql server sys.dm_os_memory_clerks memory clerks memory pressure plan cache xtp query store dmv performance monitoring scripts ·Buffer pool counters explain data cache pressure but go silent the moment memory trouble comes from somewhere else. sys.dm_os_memory_clerks lists every active memory clerk — buffer pool, plan cache, In-Memory OLTP, Query Store, query memory grants, Extended Events — and reports pages_kb per clerk. Grouping those rows …
Read MoreBreak Down Buffer Pool Memory by Database
Jul 12, 2026 / · 8 min read · sql server sys.dm_os_buffer_descriptors buffer pool data cache memory database memory dmv performance monitoring scripts ·sys.dm_os_buffer_descriptors is the DMV that maps SQL Server's data cache down to the individual page level — one row per 8 KB page currently held in the buffer pool, tagged with the database_id and file_id it belongs to. Rolling those rows up with a simple GROUP BY database_id turns millions of individual page entries …
Read MoreMeasure Disk I/O Performance per Database File
Jul 3, 2026 / · 8 min read · sql server sys.dm_io_virtual_file_stats sys.master_files disk io io stall disk latency performance database files dmv monitoring scripts ·I/O latency is where disk bottlenecks hide. SQL Server accumulates every millisecond of file-level read and write wait in sys.dm_io_virtual_file_stats — one row per database file, reset at each service restart. Joining that DMV to sys.master_files replaces raw file IDs with physical paths and file types, and dividing …
Read MoreA stored procedure that returned in 20 milliseconds yesterday is timing out today, and nothing in the code changed. Indexes are intact and statistics look current, yet the query now takes a different, slower path. One cause worth checking is a cached plan that was compiled for an unrepresentative set of parameter …
Read MoreSQL Server Identify Heap Tables Without Clustered Indexes
May 15, 2026 / · 9 min read · sql server heap tables sys.tables sys.indexes sys.dm_db_index_physical_stats sys.allocation_units sys.partitions performance database administration sql scripts ·A heap table — a table with no clustered index — stores rows in no particular order, forces full table scans for most non-indexed queries, and accumulates forwarded records every time an UPDATE causes a row to outgrow its page. This script queries sys.tables, sys.indexes, sys.partitions, sys.allocation_units, and …
Read MoreSQL Server CPU Utilization History Report
When a CPU alarm fires on a SQL Server host, the first question is whether the database engine caused it or something else on the machine did. The scheduler monitor ring buffer keeps a short history of exactly that split, and a single query turns it into one row per sample: SQL Server CPU, everything-else CPU and total …
Read More