Today, we are going to see a very simple trick about SQL SERVER Management Studio (SSMS), where we will learn how to rebuild all indexes on the table with a single click. While I considered this as a very simple trick while demonstrating this to my e-commerce client during the Comprehensive Database Performance Health Check, they were very surprised to see this option.
If you want to rebuild all the indexes on any particular table quickly, you can just do that with the help of SSMS. Just go to the table, further expand the indexing folder and right after that you can right-click on it and select the option to rebuild all the indexes. Here is the image for additional clarity.
Once you select that option it will bring up the following screen.
Over here it will display all the indexes of the table and you can just click OK and it will rebuild all the indexes. You can use the same screen to see the various details about indexes. On the same page, you will see the Total Fragmentation as well.
It is quite possible that not every time you want to use SSMS. There are chances when you just want to generate the script and execute that script. In that case, you can use the same screen to generate the script to rebuild all of your indexes as well.
Once you generate the script you can execute them in the SSMS query window.
Well, while the tip looks very simple, trust me not many people know about it. If you know any such tricks, please post them in the query window and I will be happy to post on the blog with the due credit to you. Meanwhile, If you have ever attended any of my online sessions, you might be familiar with my opinion that I do not like more than 7 indexes on any table. You can read more about that in this blog post: SQL SERVER – Poor Indexing Strategies – 10 Don’ts for Indexes
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
Quick and easy tip, thanks for sharing
Be very careful with this if you are in an AG on the cloud. This may cause the secondary to get behind if your indexes are over 1 million pages. The main issue being the through put from the disk on the cloud providers
Fair point. On AG everything changes.
It’s a disgrace. You wouldn’t need it if the engine kept things consistent. If the indexes are wrong, how do you know whether the tables have been hacked?
Kind of a pity you would have to resort to this at all, you would expect the indexing component to be able to optimise this for you.
I would love such feature – Auto Rebuild Index.
I’m sure this probably sounds like a silly question to you but why would you want to rebuild the indexes? Obviously they already exist so what is the advantage to rebuild them again?
If there are lots of insert/update/delete the index gets fragmentation and it needs to be rebuild to optimize the read.
Thanks for the reply and helping me to understand!
I have a situation, when i do rebuild on specific table is taking 11 minutes, but when i do rebuild on all tables is taking 3 minute. Why the first once is taking more than second one. Here i done rebuild offline . For the both cases i restore the database every time.
This is very difficult to answer with this limited information.
What if a table does not rebuild its index, even though I’ve tried over and over??