SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fragmentation 101


Fragmentation 101

Author
Message
currym
currym
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 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 Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 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 (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10242 Visits: 3341
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-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 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-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2071 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
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 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 (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 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