does the query optimizer know about index fragmentation

• sean hawkes

  SSC Eights!

  Points: 970

  More actions

  November 18, 2008 at 8:59 am

  #196161

  I'm new to a company and started going through some index usage using the system DMs (dm_db_index_usage_stats, dm_db_index_physical_stats, etc) and seeing that several of the indexes aren't being used. I've also noticed that framentation is really high for many of the same tables. So I'm wondering does the query optimizer take into account the fragmentation levels to make decisions and if I started a deframentation regiment would these indexes start being?

  thanks,

  Sean

• Michelle Ufford-459603

  SSCommitted

  Points: 1642

  More actions

  November 18, 2008 at 2:33 pm

  #900177

  To the best of my knowledge, no, the query optimizer does not take into account index fragmentation when determining the best query plan available.

  Good question. 🙂

  Here's a link to an index defrag script I wrote if you need one: http://sqlfool.com/2008/11/updated-index-defrag-script-2005-2008/[/url]

  Regards,

  Michelle Ufford
  SQLFool.com - Adventures in SQL Tuning