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 «««123

Fragmentation 101 Expand / Collapse
Author
Message
Posted Friday, January 18, 2008 12:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 29, 2012 11:24 AM
Points: 22, Visits: 84
I am having some "drinking from the firehose" moments where situation dictates I have to learn some things quickly and have limited tolerance for error. Hopefully, I can state my question succinctly and someone will be willing to offer advice.

I ran the DBCC scan on the DB and with some of the larger tables (500,000+ records) it was reporting over 70,000 pages with extent fragmentation in excess of 30% and logical fragmentation in excess of 40%. My understanding is this is a DB badly in need of defrag. (Side note, my understanding is that an earlier rev siilarly used would "in explicably become inaccessible and it looked like it was the tempdb" was the explanation)

Would your recommendation be to:
1) defrag with something like DK and then use REINDEX
2) RECREATE the indexes
3) something else? (to my limited understanding DEFRAG would not be a best candidate in this case)

Hopefully I have offered enough information to hazard an opinion.
Post #445078
Posted Friday, January 18, 2008 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, October 29, 2012 11:24 AM
Points: 22, Visits: 84
I did find this article very informative and, I believe, explained a seemingly complex subject quite well. But then again, I am not by training a DBA - I am just learing to dance as fast as I can.
Post #445080
Posted Wednesday, October 15, 2008 6:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 11, 2008 11:40 PM
Points: 1, Visits: 5
See BOL:
CREATE INDEX, DROP_EXISTING

"Because nonclustered indexes contain the clustering keys, the nonclustered indexes must be rebuilt when a clustered index is dropped. If a clustered index is recreated, the nonclustered indexes must be rebuilt to take the new set of keys into account"

I learnt this the hard way...


Umm, whatever you learned the hard way, I doubt it was due to DROP_EXISTING. What BOL is saying is that it rebuilds NCIs if need be, otherwise doesn't waste the time. However I see one caveat "If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index." Not something I'm ever likely to do, but figured I'd mention it, since I was about to claim that DROP_EXISTING never gets you in trouble. Anyway, unless you're doing something advanced, in a complex setup, it won't.
Post #586666
Posted Thursday, October 22, 2009 5:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:46 AM
Points: 280, Visits: 90
Hi.
3 years after its last publication, this article still sounds great :)

I think that it dismissed a T-SQL command that is a good alternative of running 'DBCC DBREINDEX (tablename)' :
ALTER INDEX ALL
ON [tablename]
REBUILD WITH ( PAD_INDEX = ON, FILLFACTOR = yourfillfactor )

Why ?
dbreindex offer the fillfactor paramter only if you specify an index; if not, or if fillfactor is not provided, it will use (and fill) 100%.
So any new insert will make a split of page (think about uniqueidentifier datatype, which will not be 'sequential' ;) ).
Alter index offer pad_index and fillfactor (default is 0, value of freespace this time).
An other great reason is that you'll be able to rebuild the indexes for a partition, so only where it must be.

what do you think about that ?



My MCP Transcript (ID : 692471 Access : 109741229)
Post #807038
Posted Monday, December 6, 2010 11:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 21, 2013 10:48 PM
Points: 36, Visits: 349
Hey Guys
Is there anyway to get these results returned by DBCC SHOWCONTIG through a system view. I've been using sys.dm_db_index_physical_stats but this doesn't give the extent fragmentation.

Reason being is that I want to run this across multiple server through a .net program which I've developed but it takes so long.
Post #1031038
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse