Guided1. Query sys.dm_db_missing_index_details and rank results by avg_user_impact × user_seeks.

SELECT d.statement, d.equality_columns, d.included_columns, s.avg_user_impact, s.user_seeks
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.avg_user_impact * s.user_seeks DESC;

Guided2. Find every index in a test database with zero reads but nonzero writes.

SELECT OBJECT_NAME(s.object_id) AS table_name, i.name
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i ON i.object_id=s.object_id AND i.index_id=s.index_id
WHERE s.user_seeks=0 AND s.user_scans=0 AND s.user_lookups=0 AND s.user_updates>0;

Guided3. Measure fragmentation on all indexes in a test database and sort descending.

SELECT OBJECT_NAME(ips.object_id) AS tbl, i.name, ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id=ips.object_id AND i.index_id=ips.index_id
ORDER BY ips.avg_fragmentation_in_percent DESC;

Guided4. Check statistics staleness on a table you’ve recently bulk-loaded into.

SELECT s.name, sp.last_updated, sp.modification_counter
FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('YourTableName');

Challenge6. A table has 12 nonclustered indexes and insert performance has degraded badly over a year. Using this module’s DMVs, design a plan to identify which indexes are safe to drop.

Challenge7. A weekly maintenance job rebuilds every index regardless of fragmentation, taking 6 hours and causing blocking. Redesign the job using the evidence-first thresholds from this module.

Challenge8. A reporting query aggregating 50 million rows for a monthly dashboard takes 40 minutes on a rowstore table. Propose a columnstore-based redesign and explain why it would help, referencing batch mode.

Break-It9. Deliberately create heavy fragmentation: insert 50,000 rows in random GUID order into a table with a GUID clustered key, measure fragmentation, then fix it with REBUILD and re-measure.

Break-It10. Deliberately induce a bad plan from stale statistics: disable auto-update statistics on a test table, bulk-load 10x its original row count, run a previously-fast query and observe the degraded plan, then manually UPDATE STATISTICS and confirm the plan improves.