Register

SQL Server Script to Rebuild and Reorganize all Indexes !!!

Softex Team will provide here insights, useful tools , tips and tricks about common software systems. We can even put some additional free software utilities for everybody to use . If you have a question about anything related to computers , just ask and we will be happy to answer :)
Site Admin
Posts: 191
Joined: 27 Feb 2012, 16:26
Location: Egypt , Cairo

SQL Server Script to Rebuild and Reorganize all Indexes !!!

Postby Softex » 13 Aug 2014, 21:57

As many of our customers run Microsoft SQL Server Databases, and since many of them have very high transaction volume and rate. In Some Cases the SQL Server Indexes created for improving performance becomes Fragmented.

Index Fragmentation means that due to high volume of data changed and index updating, the Index itself becomes very large in size (Larger than it is supposed to be), which means that index scans takes more and more time to complete holding back the execution time of different queries using the Index.

The right way of fixing this issue is to rebuild all indexes within the database so that it goes back to its original optimized state. Such process can be done while the database is online (without taking down your database), because the SQL Server engine recreates a whole new index in the background while keeping the old one in place for using, until he finish the new one and then Replace it in less than one second. So if you are asking whether you can run the below script without turning off the database then the answer is "Yes, you can".

The other question which is raised, "Do you need to to this Index Rebuilding ?" , well it is hard to say since there is no exact law for it. but the more transactions rate you have , the more frequently you need to run such maintenance script. We have seen normal databases that never needed it, and we have seen large transaction volume database that needed it twice per month.

Script Below you only Need to Change the Databases Names , Instead of "DatabaseName1" and "DatabaseName2" , Noting that this script can do the job on a single database or on 30 or 40 database in one query.


Note: Please make sure to write your database Names Correctly, if you type them wrong the system will not raise Errors and will just ignore it , so Be Careful :)


Download File from Here:

Happy SQL Administration
Softex Software House
Tech. Team
http://www.softexsw.com

Return to General Tech. Talks

Who is online

Users browsing this forum: No registered users and 1 guest