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 …

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 …

Buffer pool counters explain data cache pressure but go silent the moment memory trouble comes from somewhere else. sys.dm_os_memory_clerks is the DMV that covers everything the buffer pool doesn't — plan cache, In-Memory OLTP, Query Store, query memory grants, Extended Events sessions — by reporting pages_kb per …

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 …

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 …

A stored procedure that returned in 20 milliseconds yesterday is timing out today, and nothing in the code changed. The data did not grow meaningfully, the indexes are intact, and statistics look current — yet the query is suddenly scanning a million rows it used to seek. The usual culprit is a cached execution plan …

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 …

SQL Server records CPU utilization data internally every minute and stores the last 256 readings in a ring buffer. This T-SQL script reads that buffer to produce a CPU history report showing SQL Server CPU usage, other process CPU usage, and total server CPU load — without requiring any external monitoring tool. …

Find the Top CPU and IO Consuming Queries in SQL Server This script queries sys.dm_exec_query_stats to identify the most resource-intensive queries currently cached in the plan cache, showing logical reads, writes, CPU time, and elapsed time — with the full query text and execution plan for each. Purpose and Overview …

Find Unused Nonclustered Indexes in SQL Server This script queries sys.dm_db_index_usage_stats to identify nonclustered indexes that have never been used by any read operation since the last SQL Server restart, yet still carry write overhead with every INSERT, UPDATE, and DELETE on the table. Purpose and Overview Every …