Index Defragmentation is one of the key processes to significantly improve performance of any database. Index fragments occur when any transaction takes place in database table. Fragmentation typically happens owing to insert, update and delete transactions. Having said that, fragmented data can produce unnecessary reads thereby reducing performance of heavy fragmented tables.
I have often been asked to share my personal Index Defragmentation Script. Well, I use Automated Index Defragmentation Script created by my friend – a SQL Expert – Michelle Ufford (a.k.a SQLFool). Michelle is a SQL Server Developer, DBA, a humble blogger, and an absolute geek! She is also the President of Eastern Iowa PASS Chapter, known as the I380 Corridor Professional Association of SQL Server. Currently, she is working with large, high volume, high performance SQL Server databases at GoDaddy.com. Her nickname might be SQLFool but honestly speaking, she is one of the best Gurus of SQL. You can read her blog here.
Michelle has written an excellent article about Automated Index Defragmentation Script. I recommend this wonderful write-up to all those database developers who are searching for a good solution to improve database performance. Read her article Automated Index Defragmentation Script to equip yourself with better understanding on how to improve database performance.
Here, I would to mention an interesting question taken from the above-mentioned article, which will clearly show how powerful this Automated Index Defragmentation Script is.
How long will this Automated Index Defragmentation Script take to run?
Well, it depends. I don’t necessarily recommend running it without specifying a database; at least, not unmonitored. You *can* do that, but it could take a while. For example, to run sys.dm_db_index_physical_stats for all databases and tables, totaling 2TB, took me 4.5 hours; that doesn’t even count the actual defrags.
You can download the script from here.
Please feel free to contact Michelle or me if you have any questions and doubts regarding this script.
Reference : Pinal Dave (https://blog.sqlauthority.com)
