Once sys.dm_os_wait_stats points to locking, the next question is which index the waits belong to. The script below reads sys.dm_db_index_operational_stats for the current database and ranks each user-table index by its number of row-lock waits. Next to each count it returns the share of row-lock requests that had to …

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

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 …

A one-time data load doubled a data file overnight, the load is done, the rows are deleted, and the file is now mostly empty space. The reflex — reach for DBCC SHRINKDATABASE and give the disk back — is a common way to hurt performance without meaning to. This post explains what shrinking does to your indexes, when it …

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 on a user table — …

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 …

Tables without primary keys are heap structures with no guaranteed row ordering and no unique row identifier. They cause full table scans in queries that could use seeks, slow down DELETE and UPDATE operations, and are incompatible with SQL Server replication and Change Data Capture. This script finds every user table …