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 6, 2006 8:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 16, 2014 8:45 AM
Points: 49, Visits: 43

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




Post #248914
Posted Friday, January 6, 2006 8:52 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, February 8, 2010 8:02 AM
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.




Post #248920
Posted Friday, January 6, 2006 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 12, 2010 10:58 AM
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.
Post #248972
Posted Friday, January 6, 2006 11:33 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, March 1, 2010 10:18 AM
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

Post #249014
Posted Monday, January 9, 2006 7:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 2,907, Visits: 1,832
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
Post #249247
Posted Tuesday, January 10, 2006 12:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 9, 2009 8:13 AM
Points: 75, 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
Post #249714
Posted Friday, February 10, 2006 8:45 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 9:45 PM
Points: 229, Visits: 276

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.




Post #257555
Posted Friday, February 10, 2006 8:52 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 14, 2014 3:32 PM
Points: 917, Visits: 412

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!
Post #257561
Posted Monday, February 27, 2006 1:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:34 AM
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
Post #261749
Posted Saturday, September 29, 2007 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:54 AM
Points: 3, Visits: 374
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?



Post #404600
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse