Recently, I have received one question quite often about when to Index Reorganize and when to Index Rebuild.
I have already written about this topic earlier but it seems that many are unable to search it.
If you have any question you can search exclusively SQLAuthority at http://search.SQLAuthority.com
Reference : Pinal Dave (https://blog.sqlauthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
5 Comments. Leave new
Hi Pinal,
can you pls explain what is the storage level different between Index Reorganize or Index Rebuild. we know “Index Rebuild” means drop existing index and recreate it. but what is behind of “Index Reorganize”?
Hi Pinal,
I have created a DWH for one of our customers on SQL server 2008 R2. I have non-clustered indexes on the tables. Now I am facing issue in following conditions.
1> If I disable the indexes on these tables then my DWH get updated very fast from OLTP system but my reporting becomes slow.
2> If I don’t disable the indexes then DWH updates takes ages.
I request your help in this.
The Normal Practice is Disable All your Non-Clustered index -> perform your Data load -> Rebuild your all non-cluster indexes.
Data load speed also depend on how did you create your clustered index. If your cluster key is sequential like auto id or sequentialGUID, this will faster your insert performance.
Lot of people also follows Drop Non-clustered indexes -> Drop Clustered indexes -> Load your data -> recreate Clustered index -> recreate non-clustered indexes. this will be really help full when you do not have sequential cluster key.
Pinal i have read your lot of articles but this is my first post as a reply. correct me if i am wrong.
Hi Pinal,
My job profile involves lot of work towards SQL Server side. Everytime I am stuck with some problem, I start googling about and find one of your links that always helps. Thanks a lot for such a great material you have on your blog. Do you have anything that explains how to analyze query execution plans? If you could share that with me over my email.
Regards
Sachit
Your external link is broken.