We are troubleshooting a long-running issue with a vendor. Their software has a tendency to freeze up and stop working once or twice a week causing major disruptions to our operation. They have been unable to determine the cause despite us sending them many GBs of logs and backups of the DB. Lately they have begun suggesting the issues are with our maintenance and perhaps not with their software (despite there being no long running queries, CPU/RAM/IO pressure or even deadlocks when the problems occur). In particular they are saying our indexes are an issue.
Their favorite tool to use is DBCC showcontig despite my arguing the thing is deprecated by MS. They obsess over scan density and extent fragmentation especially. To take away the excuse I instituted some aggressive nightly maintenance that rebuilds indexes with <90% scan density or >10% fragmentation. This has somewhat thrown them off the scan density train but they remain fixated on extent fragmentation. DBCC showcontig shows high extent fragmentation even on an index that was rebuilt hours before. Below are the results of dbcc_showcontig and sys.dm_db_index_physical_stats for a table they pointed to as a "possible problem".
DBCC SHOWCONTIG
- Pages Scanned................................: 1222108
- Extents Scanned..............................: 152964
- Extent Switches..............................: 180904
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 84.44% [152764:180905]
- Logical Scan Fragmentation ..................: 3.24%
- Extent Scan Fragmentation ...................: 35.97%
- Avg. Bytes Free per Page.....................: 692.5
- Avg. Page Density (full).....................: 91.44%
sys.dm_db_index_physical_stats
index_type_desc alloc_unit_type_desc Avg_fragmentation_in_percent page_count
CLUSTERED INDEX IN_ROW_DATA 3.236803129 1222070
NONCLUSTERED INDEX IN_ROW_DATA 0.680074642 48230
NONCLUSTERED INDEX IN_ROW_DATA 0.093237195 48264
NONCLUSTERED INDEX IN_ROW_DATA 0.03315856 48253
NONCLUSTERED INDEX IN_ROW_DATA 0.194653248 48291
NONCLUSTERED INDEX IN_ROW_DATA 0.393480436 58961
NONCLUSTERED INDEX IN_ROW_DATA 0.23622292 64346
NONCLUSTERED INDEX IN_ROW_DATA 0.041445623 48256
NONCLUSTERED INDEX IN_ROW_DATA 0.701172007 59044
NONCLUSTERED INDEX IN_ROW_DATA 0.216397724 53605
Should I be concerned with my indexes? The one above is not atypical. The preferred MS DMV would appear to show it being just fine, but the vendor is stuck on that 35.97% extent fragmentation. I suspect this is just them desperately trying to find something to blame their software issues on, but if I have an actual problem I want to try and fix it.