SSMS can open a Rebuild All dialog for table indexes. An e-commerce customer discovered this shortcut during my health check.
- Expand the database, Tables and the selected table in Object Explorer.
- Right-click its Indexes folder and choose Rebuild All where available.
- Review the listed indexes and options, then use Script to inspect the generated commands.
- Execute only after assessing the workload, maintenance window and available resources.
Opening the dialog does not execute the rebuild. Rebuilding consumes resources and can hold locks. Online availability depends on operation and edition. Fragmentation alone does not establish a workload benefit.
My historical seven-index preference was a review heuristic. It was not a SQL Server limit. Assess access paths, constraints, reads and write costs for each index. Measure before and after any justified maintenance.
Related reading
- Comprehensive Database Performance Health Check
- SQL SERVER – Poor Indexing Strategies – 10 Don’ts for Indexes
A maintenance dialog is not a measured tuning result, it is an interface for an operation that needs justification.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
13 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??
After to restore databases, I have to rebuild index o not?