August 7, 2009 at 1:13 am
There is an external fragmentation occured on one of table and after running DBCC REINDEX, DBCC INDEXDEFRAG AND UPDATE STATISTICS also the fragmentation is still there with same percentage.
I am checking fragmentation from this dmv dm_db_index_physical_stats and column avg_fragmentation_in_percent is still having value 80 only.
my question is; Why DBCC REINDEX is not working ?
Suggestions will be appreciated..........
Thanks & Regards
Vinod.km
August 7, 2009 at 1:46 am
If the table is a small table (less then 8 pages), then it is located on shared extent. Shared extent stores pages of few small tables that each one of them has less then 8 pages. Since those tables don’t have there own extents, reducing there fragmentation won’t always be successful. In any case there is no reason to worry about it, since at that size it doesn’t really matter.
One small remark – You are working on SQL Server 2005. The DBCC commands to rebuild or reorganize the tables/indexes are there because of compatibility issues. Microsoft is planning to remove those commands in the future versions. Instead of using those commands, you can (and should) use the alter index statements.
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/
August 7, 2009 at 2:09 am
Thanks Adi
August 7, 2009 at 3:06 am
Small indexes often don't reindex properly, but it's not a concern. 1000 pages is usually cited as the point where one starts to worry about fragmentation in an index.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply