Rank indexes behind lock waits with operational stats, separate latches from locks, and test targeted access or row versioning fixes.
COMPRESSION_DELAY Option for Columnstore Indexes: Test It
The COMPRESSION_DELAY option keeps a closed delta rowgroup uncompressed for a number of minutes, so rows that change soon after they arrive are cheaper to modify. This post loads two demo tables, updates fresh rows in each, and shows the deleted rows that early compression leaves behind.
Fragmentation in Columnstore Indexes: Find and Fix It
Fragmentation in columnstore indexes means deleted rows that stay inside compressed row groups, and row groups that are too small. This post builds a demo table, measures the deleted rows with the row group view, and compares REORGANIZE with REBUILD.
Number of Rows Read in an Execution Plan: What It Tells You
The number of rows read shows how many rows a scan or seek touched, and it can be far larger than the rows it returned. This post builds a demo table, compares a scan with two seeks, and explains why a seek can still read too many rows.
DBREINDEX and MAXDOP: Use ALTER INDEX to Limit Parallelism
DBREINDEX and MAXDOP do not combine: DBCC DBREINDEX has no MAXDOP option, so it follows the server setting. This post shows the three errors, rebuilds a demo index with ALTER INDEX at different MAXDOP values, and measures the degree of parallelism each one used.
Instance Level Fill Factor or Index Level: What to Set
An instance level fill factor is a default for every index that sets none, and an index level setting wins. This post reads the setting, measures what each fill factor costs in pages and saves in page splits, and gives a plan for choosing the level.
sp_updatestats and Disabled Indexes: What Gets Updated
sp_updatestats and disabled indexes: the procedure updates statistics on disabled nonclustered indexes and skips a table whose clustered index is disabled. This post proves both, times the cost of disabled indexes against dropped ones on a million rows, and generates a targeted update that skips them.