index maintenance, when to rebuild and when to reorg?

Question

index maintenance, when to rebuild and when to reorg?

winston Smith

SSCoach

Points: 19500
More actions
September 27, 2009 at 1:44 pm

#385448

When i done the 70-431 exam, it was pretty clear that for internal index fragmentation, if Sys.dm_db_index_Physical_stats.avg_page_space_used_in_percent was < 60 do a rebuild, but if it was <75 but >60 do a reorg.
for external fragmentation the book states that if Sys.dm_db_index_physical_stats.avg_fragmentation_in_percent >30 then rebuild, but if its >5 and <30 do a reorganize.
I have done a few tests in preparation for taking my 70-432 ( sql 2008 equivalent) and the values at which you do a reorg or a rebuild seem to have changed, as im getting questions wrong by quoting the above values. whats worse is i cannot find out what are the recommended new values. Can anyone advise what are the correct values for index fragmentation after which a rebuild or a reorg should be performed?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

According to BOL (ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm)if avg_fragmentation_in_percent value is less then 5%, it is recommended that you won’t reorganize nor rebuild the table. If the avg_fragmentation_in_percent value is between 5% and 30 % it is recommended that you’ll reorganize the index, and if the avg_fragmentation_in_percent value is more then 30% it is recommended that you’ll rebuild the index. In real life however you should take other factors into your consideration. For example if you don’t have enterprise edition and your database should be on line 24*7, then you can’t rebuild the index. On the other hand if your database is not being use, then you can rebuild the indexes instead of reorganize them. In short there is no magic number that according to it, you’ll always rebuild or reorganize the index.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

dmoldovan SSCertifiable Points: 6334 More actions · Answer 2

See also http://www.sqlservercentral.com/Forums/Topic789128-149-1.aspx#bm789512 for some real life experiences.

winston Smith SSCoach Points: 19500 More actions · Answer 3

Adi Cohn-120898 (9/28/2009)
According to BOL ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/a28c684a-c4e9-4b24-a7ae-e248808b31e9.htm
...
...
In short there is no magic number that according to it, you’ll always rebuild or reorganize the index.
Adi

Thanks for the link. i did search and for the life of me could not find this information.

I know there is no magic number but i was looking for a solid number for two reasons:

1. i have to encode some number into my maintenance jobs.

2. Id hate to get this wrong on my exam, simply because MS expect some value that although doesnt fit all situations, they have still decided that its a magic number.

Again though, like anything i found online, this article only deals with external fragmentation and not with internal fragmentation( Sys.dm_db_index_Physical_stats.avg_page_space_used_in_percent). Id like to see sql server 2008 recomendations for internal fragmentation.

Thanks again.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

You can find this information in the second table in the section that is called “Detecting Fragmentation”. Also notice that this was taken from that same article:

“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.”

As for a “magic number for internal fragmentation” – this is even harder then giving a magic number for external fragmentation because of the different nature of data modifications in each table. I don’t think that you’ll find any article that will give you a number. There are to many variables to get one number for all cases.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/