To check index fragmentation with row count, read sys.dm_db_index_physical_stats in SAMPLED mode and keep the page count and row count next to each percentage. This post builds a demo with three tables, lists every index, filters out the tiny ones, loops over every online database, and rebuilds the one index that matters.
Index Size Estimate: Sizing an Index Before You Create It
Before you build an index, estimate its size and say what you assumed. Then check the guess against real pages on a copy.
SQL SERVER – Parallelism for Heap Scan
Two DBAs asked me if their table needed a clustered index. Our conversation during a Health Check turned into a lesson on parallelism for Heap Scan.
Disk Space Needed to Build a Clustered Index on a Big Table
A clustered index build holds the old table and the new index at the same time. Here is a small demo of that peak, and what to check first.
SQL SERVER – What is Forwarded Records and How to Fix Them?
Forwarded Records in heap tables still puzzle many SQL Server users, years after I first wrote about them. Here is what they are and how to fix them.
How to List All the Nonclustered Indexes of Your Table? – Interview Question of the Week #155
How do you list all the clustered and nonclustered indexes of your table, including columnstore? Here is the script I use in my performance health checks.
How to List All ColumnStore Indexes with Table Name in SQL Server? – Interview Question of the Week #153
Question: How to List All ColumnStore Indexes with Table Name in SQL Server? As ColumnStore Indexes are getting more and more popular.