VA Index Fragmentation Auditor
HEALTHY (< 5%): Physical pages are well-organized on disk. I/O performance is optimal.
WARNING (5-30%): Leaf-level pages are scattered. Consider a REORGANIZE to defragment without locking.
CRITICAL (> 30%): High physical disorganization. A REBUILD is required to restore storage efficiency.
DBA Tips: Resource Governance
- The physical Penalty: Scattered data forces the storage engine to perform more physical reads, driving up IO_LATENCY.
- Size Threshold: This auditor ignores tables with < 100 pages, as fragmentation in tiny tables does not impact VA production speeds.
- ONLINE = ON: Mandatory for VA rebuilds to ensure Veterans and staff are not blocked during maintenance.
Needs Rebuild
1
Needs Reorganize
0
Healthy Indexes
7
Avg Frag %
12.40%
Frag Distribution
| Status | Table | ID | Index Name | Frag % | Pages | Records | DDL ACTION |
|---|---|---|---|---|---|---|---|
| REBUILD (CRITICAL) | Veterans_Scrambled | 2 | IX_Scramble_Code | 97.94% | 243 | 0 | ALTER INDEX [IX_Scramble_Code] ON [dbo].[Veterans_Scrambled] REBUILD WITH (ONLINE = ON); |
| OK | Clinics | 0 | --- HEAP --- | 0.56% | 1,429 | 0 | -- No Action Required -- |
| OK | customers | 1 | PK_customers | 0.36% | 3,902 | 0 | -- No Action Required -- |
| OK | SniffTest | 1 | PK__SniffTes__3214EC27744694F6 | 0.25% | 786 | 0 | -- No Action Required -- |
| OK | Veterans_Scrambled | 1 | PK__Veterans__2556B80EF4497B51 | 0.06% | 21,779 | 0 | -- No Action Required -- |
| OK | AgencyStressTest | 0 | --- HEAP --- | 0.00% | 652 | 0 | -- No Action Required -- |
| OK | Veterans | 0 | --- HEAP --- | 0.00% | 1,539 | 0 | -- No Action Required -- |
| OK | MedicalRecords | 0 | --- HEAP --- | 0.00% | 602 | 0 | -- No Action Required -- |
Reference: Raw Truth Verification Query
SELECT OBJECT_NAME(object_id) AS [Table], index_id, avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') WHERE page_count > 100 ORDER BY avg_fragmentation_in_percent DESC;