[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.
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.
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)