Sql-server – REORGANIZE seems to work as well as REBUILD all the time

indexsql serversql-server-2012

On this MSDN Page it says if you should reorganize or rebuild based on the amount of fragmentation:

 5% to 30%   ->   ALTER INDEX REORGANIZE  
 Over 30%    ->   ALTER INDEX REBUILD WITH (ONLINE = ON)*

However, we have noticed that even with really high fragmentation (over 95%) on large and small tables, REORGANIZE works fine. (The fragmentation goes down to less that 1%).

Why does the MSDN page say this? Is it not supposed to work like it is for me?

Or is there a drawback I am missing? (A hidden problem if I don't rebuild?)

Best Answer

Let me quote another part of that same page, which basically says IT DEPENDS - emphasis mine:

These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment.

There's not really a good answer for this except "it depends"... those are just general guidelines and may or may not be appropriate depending on the number of rows, the columns in your index, their data types, how wide the index is, what else is getting written to disk in between writes to those tables, etc.

Like how big should I size tempdb, what is a good threshold for PLE, and what type of car should I buy, the answer is the same: IT DEPENDS.

Related Question