July 8, 2010 at 1:52 pm
Hi friends,
I have executed sys.dm_db_index_physical_stats (22,null,null,null,null)
and i got the result in which avg_fragmentation_in_percent (which is one of the column)= 75
and i have exec dbcc dbreindex, dbcc indexdefrag and also manually rebuild the index. But i didn't find any difference on this fragmentation value. does it will change?
Usually we will reorganize the indexes if the fragmentation in bet 20-30% and rebuild if the fragmentation >30% my question is where i can find the exact fragmentation information? and where i can find the changes in that value after i rebuild or reorganize my indexes?
one more task : I want to create a maintenance plan where i wanna reorganize the index or rebuild the index based on fragmentation information. Is this possible? if yes how? Plz suggest me.
Sam
July 8, 2010 at 2:49 pm
You find the exact fragmentation in sys.dm_db_index_physical_stats, in the avg_fragmentation_in_precent column. Check it before and after rebuild to see the change.
If the fragmentation's not decreasing, there's a common reason: the index is too small. Due to the way SQL assigns the first few pages of a table, rebuilding an index under 20 pages often has no effect. It is commonly recommended not to bother rebuilding indexes under 1000 pages
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2010 at 6:43 am
Check the page count , if it is near about 1000 as gail suggest, there would be no impact of defragmentation.
select object_name(object_id),page_count,* from sys.dm_db_index_physical_stats (22,null,null,null,null)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply