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 …

Every UPDATE and DELETE on a system-versioned table leaves a queryable copy of the previous row, with no triggers and no hand-built audit table. SQL Server records the period each row version was valid and answers point-in-time questions through a single FOR SYSTEM_TIME clause. The catalog view sys.tables lists which …

Documenting a database starts with a data dictionary: every table, every column, its data type, length, nullability, and default. SQL Server exposes this through the ANSI-standard INFORMATION_SCHEMA views, which produce a portable schema report that reads almost like English and runs unchanged on other ISO-compliant …

Statistics are the optimizer's picture of the data, and that picture ages every time rows change. When a table grows faster than its statistics are refreshed, plans keep being built for the row counts it held weeks ago. Refreshing the statistics is often the fix, and the work is knowing which ones to refresh and with …

Which columns make up a table's primary key, and in what order are they declared? For a single table, the system stored procedure sp_pkeys answers in one line; for a database-wide inventory, the catalog views behind it give you full control. This post covers both, from the quick lookup to the complete metadata query. …