Defragging Indexes in SQL Server 2005

There are two ways to optimize your indexes in SQL Server 2005:

  • ALTER INDEX … REORGANIZE
  • ALTER INDEX … REBUILD

Which one should you choose? According to BOL, it depends on how fragmented they are. The avg_fragmentation_in_percent column from the sys.dm_db_index_physical_stats() system function lets you know how fragmented your indexes are. If the value is below 5.0, then there is no need to defragment the index. If the value is between 5.0 and 30.0, then “ALTER INDEX … REORGANIZE”, is the best option. If the fragmentation is greater than 30.0, then “ALTER INDEX … REBUILD” is the best option.

REORGANIZE has a couple of benefits. REORGANIZE will log only the pages that changed, while REBUILD will log the whole index. REORGANIZE can be done online, while REBUILD can’t (unless you add the ONLINE=ON option for Enterprise Edition installations).

However REBUILD will update the statistics of the index which lead to better execution plans, while REORGANIZE does not.

About Clay Lenhart

I am a DBA/Technical Architect for Latitude Group and love technology.
This entry was posted in SQL Server Administration. Bookmark the permalink.

9 Responses to Defragging Indexes in SQL Server 2005

  1. Pippa Hart says:

    It’s impressive that you are getting thoughts from this post as well as from our dialogue made here.

  2. Luke Davies says:

    In the awesome scheme of things you’ll secure a B- for hard work. Where exactly you actually lost me personally ended up being on all the particulars. As they say, the devil is in the details… And that couldn’t be more true right here. Having said that, let me tell you just what did deliver the results. The authoring can be rather engaging which is probably the reason why I am making an effort to comment. I do not make it a regular habit of doing that. Next, despite the fact that I can certainly notice a leaps in logic you come up with, I am not certain of just how you appear to unite the points which in turn make the conclusion. For the moment I will subscribe to your position but trust in the foreseeable future you link the dots much better.

  3. Max Lee says:

    whoah this weblog is great i seriously like studying your posts. Remain up the excellent paintings! You understand, lots of people are looking about for this information and facts, you could enable them considerably.

  4. I keep trying to subscribe to the Letters thing, but there’s nowhere to do so! After i sign in, under the MANAGE YOUR SUBSCRIPTION weblink, there’s only my Book Club subscription. How do I add the Letters thing for $5?

  5. Jake Mathis says:

    I got this site from my pal who informed me about this site and now this time I am browsing this web page and reading very informative content at this time.

  6. Hello.This post was really remarkable, particularly since I was searching for thoughts on this issue last Saturday.

  7. [url=http://www.gowatchs.com/brand-141.html]梅表会社総裁のダニエル・史洛普(ダニエルSchluep)初めて見张奇開の絵に一目惚れしてパンダと感動に加え、パンダはかわいくて、おとなしくて、平和のイメージは、更に勾よう大衆に絶滅動物の生態環境の注目。そこでダニエル・史洛普決定と张奇開一回風変わりな協力運用精妙な発想と技巧、「大気圏に再突入するNo . 1」をテーマに、高度な油絵結合タブ工芸とデザインの文字盤に。シャネル 時計 コピーダニエル・史洛普はずっと芸術好きで、张奇開さんが作品の中で体現してパンダの熱愛と時間と空間のテーマで議論し、彼は深く触れ。で张奇開にとって、今回スイスと梅表国際時計ブランドと提携し、さらに発揚と中華芸術交流促進を絶好のきっかけ。[/url]

  8. [url=http://www.gginza.com/%E6%99%82%E8%A8%88/%E3%83%AD%E3%83%AC%E3%83%83%E3%82%AF%E3%82%B9/daytona/8cc724a5a792d955.html]私が初めて会ったエマニュエル・ブーシェの2012年の彼とハリー・ウィンストン作品12の時計が提示した(ここでは実際に戻っている)。の作品・プロジェクトの一部である腕時計メーカーのための巨大な名誉です、そして、ブーシェが存在する間の小さいが、由緒あるクラスは、彼らの創造力と伝統的技術と価値の全くの応用のための最も知られている現代の時計メーカー。[/url]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>