Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fragmentation 101


Fragmentation 101

Author
Message
Paul G-468777
Paul G-468777
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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.
Paul G-468777
Paul G-468777
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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.
toolmakersteve1
toolmakersteve1
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Dude76
Dude76
SSC Veteran
SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)SSC Veteran (280 reputation)

Group: General Forum Members
Points: 280 Visits: 94
Hi.
3 years after its last publication, this article still sounds great Smile

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' Wink ).
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)
matthew.peters
matthew.peters
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search