February 14, 2009 at 12:21 am
Hi,
I would like to know How and when we need to rebuild indexes, reorganise indexes , defragindexes in SQL Server 2005. Please suggest a link or article which explains this.
With Regards
Dakshina Murthy
February 14, 2009 at 1:53 am
You may want to start by searching this forum, a ton on info on those subjects. 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 14, 2009 at 7:34 am
dakshinamurthy
I would like to know How and when we need to rebuild indexes, reorganise indexes , defragindexes in SQL Server 2005. Please suggest a link or article which explains this.
ALTER INDEX....REBUILD - For rebuilding indexes
ALTER INDEX....REORGANISE - For Reorganizing them
Run : sys_dm_db_index_physical_stats and check for avg_fragmentation_in_percent column
Typically its: lesser then 30% reorganize indexes, greater than 30% rebuild indexes
I would do it once in a week or off time daily if you have any . And also it depends on how frequent your data gets fragmented, i mean heavy inserts per day? analyze your data and plan it.I would set a job in my regular maintenance plan once decided whether to rebuild or re organize.
Be cautious about your log file before rebuilding them- plan according to your DR plan
February 16, 2009 at 2:32 am
Hi,
Thanks for the help. I have excuted the Rebuild Command for certain tables (20 + ) whose avg_fragmentation_in_percent varies from 35 to 80 for certain tables. Even after rebuilding indexes, the avg_fragmentation_in_percent has not changed for the tables, what i have to do or is there any thing which we have to look in to, please suggest.
With Regards
Dakshina Murthy
February 16, 2009 at 2:35 am
What is the size of your table that has 35-80 fragmentation? because if the size of the table is small enough then its quite common to have that fragmentation and you do not need to defrag
February 16, 2009 at 2:52 am
The size is in KBs,
February 16, 2009 at 3:04 am
How many pages? Can you query it please? If the table is 1000 pages or more and if still the fragmentation is same then I would worry about it. Any thing smaller than that, I would not worry about it.
February 16, 2009 at 3:14 am
Hi,
The page counts are less than 1000. Thanks a lot, it was really helpfull for me. Thanks for the instant reply and help.
With Regards
Dakshina Murthy
February 16, 2009 at 3:28 am
My Pleasure!!! You dont have to worry if it is lesser than 1000 pages. The pages would be loaded to the memory anyway:)
March 15, 2011 at 11:02 am
Maechismo_8514, I have a question about your comment "the pages would be loaded to the memory anyway"
I typically REORG fragmented indexes that are > 50 pages or more because in our environment I've noticed it makes a significant difference. However, what do you mean that they will be loaded to the memory?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply