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

Storage budgets shrink faster than databases grow. Uncompressed tables and indexes often carry two to four times the physical page count that row or page compression would require — a gap that widens with every batch load or continuous INSERT. sys.dm_db_index_physical_stats joined to sys.partitions surfaces every …

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

Plenty of SQL Server scripts still in circulation read rowcnt and dpages straight out of sysindexes — a habit inherited from SQL Server 2000, where that table was the canonical place to find row counts and page totals. The object survives only as the sys.sysindexes compatibility view, is flagged for removal, and …

Three catalog views, sys.objects, sys.tables, and sys.indexes, answer most day-to-day questions about what a database contains. This script joins them with sys.schemas and sys.partitions into one inventory: every user table, its schema, its dates, its row count, and each index defined on it. The row count comes from a …

Open the SQL Agent job history on an instance that has been running for a decade and you will still find DBCC DBREINDEX in a nightly maintenance step, quietly doing its job. It works — which is exactly why nobody has touched it. But it has been deprecated for the better part of twenty years, it locks tables it does not …

Duplicate and overlapping indexes waste storage, slow down every write, and split optimizer statistics across redundant copies. This T-SQL script compares each non-heap index in the current database on its first sixteen key columns and surfaces any pair of indexes that overlap, giving the DBA a short list of cleanup …

Knowing how much space each table and each index consumes is the foundation of capacity planning, archive policy, and index cleanup decisions on any SQL Server instance. This T-SQL script reads the partition-level storage statistics out of sys.dm_db_partition_stats and reports the size of every index — clustered, …