VA Index Usage Auditor
Healthy: Balanced activity
Warning: Write or Scan Inefficiency
Critical: Structural or Zombie Debt
DBA Tips: Index Life-Cycle
- Structural Debt (Heaps): These lack a primary key; SQL must scan the entire pile to find anything[cite: 1360, 1406].
- Transactional Debt (Zombies): Zero reads detected. These are "Write Taxes" without retrieval benefit[cite: 1409, 1448].
- The Uptime Rule: Stats reset on restart; ensure server uptime > 7 days for accurate pruning[cite: 1021, 1344].
Critical Heaps
6
Zombie Indexes
6
Write-Only
3
Scan-Heavy
1
Usage Distribution
| Status | Table | Index | Type | Seeks | Scans | Lookups | Reads | Writes |
|---|---|---|---|---|---|---|---|---|
| HEAP (CRITICAL) | AgencyStressTest | --- HEAP --- | HEAP | 0 | 0 | 0 | 0 | 0 |
| ZOMBIE (CRITICAL) | PennyTestLoad | PK__PennyTes__3214EC2748742BD5 | CLUSTERED | 0 | 0 | 0 | 0 | 0 |
| ZOMBIE (CRITICAL) | PennyTestLoad | IX_PennyTest_Date | NONCLUSTERED | 0 | 0 | 0 | 0 | 0 |
| HEAP (CRITICAL) | DashboardTest | --- HEAP --- | HEAP | 0 | 0 | 0 | 0 | 0 |
| ZOMBIE (CRITICAL) | products | PK_products | CLUSTERED | 0 | 0 | 0 | 0 | 0 |
| ZOMBIE (CRITICAL) | Customers2 | PK__Customer__A4AE64B8BCE83EF3 | CLUSTERED | 0 | 0 | 0 | 0 | 0 |
| ZOMBIE (CRITICAL) | StoredProcedureVersionHistory | PK__StoredPr__4D7B4ADDC8AA6421 | CLUSTERED | 0 | 0 | 0 | 0 | 0 |
| HEAP (CRITICAL) | SalesTest | --- HEAP --- | HEAP | 0 | 0 | 0 | 0 | 0 |
| ZOMBIE (CRITICAL) | SniffTest | PK__SniffTes__3214EC27744694F6 | CLUSTERED | 0 | 0 | 0 | 0 | 0 |
| WRITE-ONLY | Veterans_Scrambled | PK__Veterans__2556B80EF4497B51 | CLUSTERED | 0 | 1 | 0 | 1 | 6 |
| WRITE-ONLY | Veterans_Scrambled | IX_Scramble_Code | NONCLUSTERED | 0 | 1 | 0 | 1 | 3 |
| HEAP (CRITICAL) | Clinics | --- HEAP --- | HEAP | 0 | 50 | 0 | 50 | 0 |
| HEAP (CRITICAL) | MedicalRecords | --- HEAP --- | HEAP | 0 | 50 | 0 | 50 | 0 |
| SCAN-HEAVY | sales | PK_sales | CLUSTERED | 0 | 80 | 0 | 80 | 0 |
| WRITE-ONLY | Appointments | PK__Appointm__EDACF695EC10A96C | CLUSTERED | 0 | 90 | 0 | 90 | 10,000 |
| HEAP (CRITICAL) | Veterans | --- HEAP --- | HEAP | 0 | 100 | 0 | 100 | 0 |
| OK | customers | PK_customers | CLUSTERED | 180 | 100 | 0 | 280 | 0 |
Reference: Raw Truth Verification Query
SELECT OBJECT_NAME(i.[object_id]) AS [Table], i.name AS [Index], i.type_desc, s.user_seeks, s.user_scans, s.user_lookups, s.user_updates FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id AND s.database_id = DB_ID() WHERE OBJECTPROPERTY(i.[object_id], 'IsUserTable') = 1;