Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC REINDEX Expand / Collapse
Author
Message
Posted Friday, August 7, 2009 1:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:14 PM
Points: 109, Visits: 317
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
Post #766721
Posted Friday, August 7, 2009 1:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 2,112, Visits: 5,498
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/
Post #766732
Posted Friday, August 7, 2009 2:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:14 PM
Points: 109, Visits: 317
Thanks Adi
Post #766745
Posted Friday, August 7, 2009 3:06 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:41 AM
Points: 40,187, Visits: 36,593
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #766770
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse