Conor vs. Index Fragmentation in Plan Selection

(more SQL Bits questions)

Does the SQL Server Query Optimizer consider index fragmentation in plan selection? 

 

No, it does not directly care.  The optimizer does currently consider the number of pages consumed by the index.  So, a heavily fragmented index with lots of unused space might be considered less optimal than an otherwise equivalent index with less fragmentation because it is on fewer disk pages.  The optimizer does not really consider the fragmentation to determine whether there will be more random IOs.  Therefore, you should plan to remove that fragmentation during regular maintenance windows if you feel that it is significant to the performance of your application.  (I won’t give any specific guidance on this – you should go read the blogs from people in the Storage Engine and see what their best practices are).

 

Happy Querying!

 

Conor