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 …

A transaction that never commits keeps every log record written after it pinned in place, no matter how much work piles up behind it. sys.dm_tran_active_transactions lists every transaction currently open on the instance, but on its own it only shows a transaction ID and a start time — pairing it with …

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 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 …

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 …

Storage errors do not always surface as immediate failures. When SQL Server hits an 823 or 824 error while reading a database page, it records that page in msdb.dbo.suspect_pages — a system table in msdb that keeps one row per suspect page until someone deletes it. Querying this table is the fastest first pass in any …

Database Mail is switched off on a new instance, and sp_send_dbmail cannot work until an SMTP account, a profile that holds it, and a grant that lets users send through that profile all exist in msdb. The script below enables the feature, builds those three objects, sends a test message, and reads the queue and the …

Heaps and indexes that were never compressed keep occupying full-size pages, and nothing in a default database flags them. sys.dm_db_index_physical_stats joined to sys.partitions lists the uncompressed heaps and indexes above a size threshold, and the script below turns that list into ready-to-review ALTER INDEX ... …

A table inventory that spans every database on an instance cannot be answered from one database context, because the table catalog lives inside each database. The pattern below reads the database list from sys.databases, builds one INSERT ... SELECT per online user database against that database's sys.tables and …