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


Fragmentation 101


Fragmentation 101

Author
Message
currym
currym
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 57

Good article! Really liked the fact that it clearly indicated problems and solutions (to include pros and cons). Thank you!





vambati
vambati
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 15

We recently migrated from SQL 7 to SQL 2000. In SQL 7 whenever a clustered index was rebuilt the NC would get rebuilt, but in SQL 2000 i do not see the NC being rebuilt. Atleast not in last 2 weeks of what i have seen. So is this an enhancement in SQL 2000 or does that mean the keys havn't changed to affect rebuild of NC?

Very good article and the dicussion is also providing lot of information.





David Urban
David Urban
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 20
The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though.
Craig L. Waters
Craig L. Waters
SSChasing Mays
SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)SSChasing Mays (613 reputation)

Group: General Forum Members
Points: 613 Visits: 2

We have our SQL databases on RAID sets on a SAN. I assume, therefore, that the disk defragmentation discussion is moot, since files on this hardware are fragmented by design. I wouldn't know where to begin to figure out which disks and how contiguous the files were.

Given that, one shouldn't spend too much time trying to optimize table or index storage either, other than having fillfactors that are consistent with transaction load, right?

Thanks


David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3696 Visits: 3120
I have read an article on physical file defragmentation on a SAN or RAID system www.raxco.com/products/perfectdisk2k/whitepapers/pd_raid.pdf.

The gist of RAID/SAN defrag is that
a) Not all defrag tools are compatible with SANS/RAID arrays.
b) It is the LCN numbers that get defragged rather than the physical files and this improves performance.

There is a benefit in performance of having the data striped across the disks in a RAID/SAN but the LCN benefit from having contiguous blocks.

Has anyone got any practical experience with this?

LinkedIn Profile

Newbie on www.simple-talk.com
Alex-217289
Alex-217289
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 3
Realizing that fragmentation is an inevitability, we allocate time each night for a complete run of DBCC DBREINDEX on each database. Of course, as servers fill and client requests increase, the maintenance windows shrink (what a shame) so continuing this practice may become rather difficult, forcing a switch to a 1x a week schedule or something.

There is a gold lining though. As a couple others have mentioned, it is possible to do a complete defrag (logical and physical) whole the databases are online. Most "modern" tools such as D.K. allow this.

We've recently upgraded to tthe latest version of this tool and find it not only very effective at defragging tasks but much more efficient than expected. As always, I would suggest plenty of development testing befiore using on your production servers but to date, we've had no issues in any of our environments. There is a 30 day free trial. Use it!


Cheers,

Alex


Rogue DBA
icata
icata
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 295

For file defragmentation, I heard indeed that Diskeep does a good job been able to defrag files in use and base on a schedule, never use it though.

Regarding

"The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though."

I use it on one of our production servers and during the process it made the tempdb inaccessible. Even if I try to stop the process the SQL Server was inaccessible. I had to restart it.





Crispin Proctor
Crispin Proctor
SSC Eights!
SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)SSC Eights! (919 reputation)

Group: General Forum Members
Points: 919 Visits: 414

Regarding

"The defrag that comes with win2k3 is capable of defragging the files while sql server is online, it's a huge I/O hit and the server is pratically unusable; it's possible though."

Diskeeper monitors the IO queue and throttles back when this rises so the hit to the machine is near nothing.

You could, essentially, run the defragger every hour. Why though...






Cheers,
Crispin


I can't die, there are too many people who still have to meet me!

It's not a bug, SQL just misunderstood me!
Greg Hartlaub
Greg Hartlaub
SSC Veteran
SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)SSC Veteran (219 reputation)

Group: General Forum Members
Points: 219 Visits: 36
Hi,

I'm having negative experience with Diskeeper. It's locking us out of SQL when it's defragging the server. Defrag is scheduled weekly on Saturdays when we usually aren't hitting the db.

I'm running Win2003 Server and SQL 2000 on Raid 5 array. Two db's with the problem are 66 and 50 Gb each. Hard drive is 410 GB with 50% free.

First noticed it 3 weeks ago when a db maintenance job blew up. Changed it to complete before the defrag began and this worked.

However, a few data entry folks were in this Saturday am and could no longer access the db as soon Diskeeper started. Totally locked out. And PO'd.

From the thread it sounds like this shouldn't happen with Diskeeper. Is there an option/setting that we're missing?

Other than scheduling the defrag for later on Saturday, looking for a better solution.

Any ideas? Our network admin told me SQL has a defrag function. Since I already regularly defrag indexes weekly I'm not sure what he's talking about.

Thanks


Greg H
Larry Leonard-399461
Larry Leonard-399461
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 534
There are at least five or six factual errors in this article, which I was going to list but there got to be too many. What's the definition of "internal fragmentation" again?



Pinch
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