SQL SERVER – Index Fragmentation Next Steps – Notes from the Field #020

[Notes from Pinal]: Indexes are considered as a silver bullet to performance tuning and do amazing job when they are properly created. Just like any other engine they need maintenance and tuning. As we get more data indexes start to get fragmented and performance starts to degrade. I asked direct questions to my friend Tim about fragmentation and he gave me a very interesting answer.

Linchpin People are database coaches and wellness experts for a data driven world. In this 20th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) shares very interesting conversation with me. I asked:

Pinal: “Tim, what would you do if your indexes gets fragmented?”


When analyzing systems I often find that little maintenance is being performed. Although proper backups are my number one priority when checking out a system, index maintenance is also very important.  I have a script that I run that check for index fragmentation and very often I find extremely high fragmentation levels for most of the tables in the database.

Finding high fragmentation is very common and the fix is quite easy. I simply have to apply a maintenance script to clean up the fragmentation.  The path I take can vary depending on the size of the database and the edition of SQL being ran.  Rebuilding indexes on standard edition will have a vastly different impact, then an online index rebuild on enterprise edition.  I usually take a surgical approach on a large database with very high levels of fragmentation until I get the fragmentation under control. Once the indexes are at a more manageable level of fragmentation I allow the script to keep things in check.

There are numerous products on the market that can help and there are free products as well.  Microsoft even includes an option within Database Maintenance plans; however, I personally use Ola Hallengren’s http://ola.hallengren.com/scripts for Index maintenance and for updating statistics.  Ola also includes a comprehensive backup solution, but unless the client needs a complete overhaul I typically don’t make changes there.

The great thing about this solution is the ability to rebuild when needed and reorganize when needed. You get to choose which option based on the level of fragmentation.   This solution gives you a great deal of flexibility with how it is run.  When running index maintenance on a system that is highly fragmented for the first time, even with the online options, be careful to monitor your transaction log size.  You will also need to have extra space in your data file for index rebuilds as well.

Regardless of which solution you choose for your index maintenance, the important thing is that you have a proper solution in place to deal with fragmentation.  What causes fragmentation in the first place, good for starters inserts, updates, and deletes.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

5 thoughts on “SQL SERVER – Index Fragmentation Next Steps – Notes from the Field #020

  1. Hi! One important thing: With the database shrink, the index fragmentation growts up to 99% (After shrink need rebuild indexes or dont use shrink).

    I have a question: In SQL Server Enterprise 2008 the online-rebuild function not working properly. The index rebuild (which run several hours) blocks almost all insert operations. What could be the reason? Thanks for help!

    Like

  2. We suffer from not only fragmentation at my shop but also from a poor indexing strategy from the start. It’s always been additive and I believe we have tons of unused indexes that add costly time to our maintenance. That’s where the real trouble starts as our clients are multi-national and our systems in use 24/7. I never get to a time where locking tables for rebuilds is acceptable and our db team isn’t experienced enough to know the difference between online and offline re-indexing. I’ll have to check out Ola’s scripting repository and see if I can help them out.

    Like

  3. Pingback: SQL SERVER – Round Up From Notes from the Field of Blog Posts of Tim Radney | Journey to SQL Authority with Pinal Dave

  4. This is my favorite Post Because of http://ola.hallengren.com/scripts. when I have seen these scripts then I surprised and I have implemented job in my server for Index maintenance and update statistics before this my index maintenance plan was taking 24 to 25 hours but after implementing using Ola’s Scripts it only taking 14 to 15 hour.
    Basically these script Rebuilt or reorganize only those indexes which needed .
    such as if index fragmentation is between 5% to 30% it reorganizes
    if more than 30% it rebuilds otherwise it did nothing if fragmentation is less than 5%
    Amazing …..

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s