Search This Blog

Friday, December 14, 2012

be wary of the results of sys.dm_db_index_physical_stats

my coworker (@developingjim) and his team was troubleshooting a client database production down issue this past weekend and discovered something that neither he nor i were aware of - the sys.dm_db_index_physical_stats function only scans index parent-level pages (leaf-level +1). the results of this versus running the function in DETAILED mode (which scans all pages and returns all statistics) can be drastically different. The image below shows two queries and the results for each:


The first query lead the team to believe that index fragmentation was not an issue; the second revealed that this was far from the truth and the crisis was resolved after the massive fragmentation was addressed

No comments: