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

[Notes from Pinal]: Indexes are considered as a silver bullet for 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 index 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 (https://blog.sqlauthority.com)