Why should you care?

Heavily fragmented indexes can slow scans. Blind REORGANIZE/REBUILD everywhere also hurts — measure first.

See it live — copy this example

Run in SQL Server Management Studio (SSMS) or Azure Data Studio.

USE DataVerse;
SELECT
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Orders'), NULL, NULL, N'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

What happened?

• The DMV reports fragmentation and page counts.
• Tiny indexes (few pages) are not worth rebuilding even if the percent looks high.

Practice next

1. Run the fragmentation query on your database.
2. Ignore indexes with very small page_count.
3. REORGANIZE a mildly fragmented NC index.
4. Switch mode to SAMPLED for more detail in lab.
5. ALTER INDEX … REORGANIZE vs REBUILD on a test index.

Remember

Measure fragmentation with the DMV. Act on large indexes past thresholds. Missing indexes matter more than perfect defrag.

Weekly index health job

DataVerse Agent job rebuilds only indexes over 30% frag and 1000+ pages.

Outcome: Maintenance windows stay short; plans stay healthy.