SQL SERVER Management Studio – Rebuild All Indexes on Table

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.

SQL SERVER Management Studio - Rebuild All Indexes on Table rebuild-all1

Solarwinds

Once you select that option it will bring up the following screen.

SQL SERVER Management Studio - Rebuild All Indexes on Table rebuild-all2

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Count NULL Values From Column
Next Post
SQL SERVER – Msg 17177 – This Instance of SQL Server Has Been Using a Process ID of %s since %s (local) %s (UTC)

Related Posts

9 Comments. Leave new

  • Mohammed Benabdallah
    March 7, 2020 1:31 am

    Quick and easy tip, thanks for sharing

    Reply
  • Christopher D Wolff
    March 7, 2020 8:20 pm

    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

    Reply
  • Malcolm K Crowe
    March 8, 2020 12:35 am

    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?

    Reply
  • 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.

    Reply
  • Anthony Griggs
    March 8, 2020 7:43 am

    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?

    Reply
    • If there are lots of insert/update/delete the index gets fragmentation and it needs to be rebuild to optimize the read.

      Reply

Leave a Reply

Menu