Disk Defrag Tools on SQL Server system
-
I'm not sure I am in the right location so if not I apologize.
I am looking for opinions on using disk deframentor (windows standard) or a 3rd party utility to run disk defrag while SQL Server 2000/2005 is running and the databases are in use. Do you know of any pit falls to doing such activity.
Thank you
-
You need to shut SQL Server down for this to be safe and effective. I know people don't and vendors say it will work, but I'm not sure I trust this. If you want to run it on things outside the MDF/LDF, exclude those files.
It might help, I've seen differing opinions on what happens, but it shouldn't hurt if SQL is shut down.
good backup
good Resume
you only need one.
-
Diskeeper does claim that they can safely defrag databases while they are online. I used their product for years at my previous employer without any issues. I know it was defragging live databases since I kicked it off manually several times and watched what it was doing. I never had a database corrupted as a result of allowing Diskeeper to do its job.
-
I've been running the new Diskeeper on my lab SQL box for a couple months now and it's been very stable. And I'm pushing thousands of transactions against it every hour.
I know there have been problems with it corrupting your DB in the past, but that problem wasn't ubiquitous. It was something that could happen, not something that was guaranteed to happen. I'm not sure how they've solved that problem though. I've talked to the Diskeeper developer who writes this and he's not saying anything. Oh well.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Not to be done whilst the database is busy but you can use the w2k3 defrag with sql server on-line, been doing this since around 2003!
Like any such task it will interfere with the running of your database(s). If your drives are very bad then you'll need to do this offline, but basically if you keep on top of it ( by sizing your databses so they don't grow ) it's quite easy. It helps to have fast arrays, preferably raid 10, and at least 25% free space.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks everyone!
These databases have to be online 99.999% of the time. I am going to test all the options suggested to determine which one works best in this situation.
Thanks again.
-
I'm thinking about if file level fragmentation really is a problem on sql server data and log files if you grow your files in large chunks and if you do not shrink your files. What is your view on this?
Ola Hallengren
-
They do grow but we do shrink them. We are testing DiskKeeper now.
Thank you
-
My theory is that if you pregrow your data and log files and never do any shrinking, then maybe you don't need DiskKeeper.
Ola Hallengren
-
Even if you pregrow your files you'll still get fragmented unless they're all on separate disks. It's impossible not to because if you ever add a file it'll come in a fill the gaps left by the others. Not to mention the fact that you just can't grow them all to the exact size they'll ever be.
It can happen, but I wouldn't count on it.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Ah but if you size your files so growth is rare - once you've defragged they'll stay that way - you only have gaps if the drive is fragmented.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I can see how defragmenting works on a single physical drive (after all, that's where it all started).
I've always wondered though - how does it handle drive arrays? Windows (and by extension, Diskeeper et al) may see a single 400GB drive, but if the data is striped across 5 drives, how is defragging going to help? What's to say that by running a defrag tool, you aren't just moving data from one random area of each disk to another random area of each disk? Given that Windows is only seeing what the RAID controller is telling it is there (e.g., a single disk instead of 5), what's to stop the RAID controller + disk just firing the data onto whatever cluster it sees fit?
Scott DuncanMARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare -
There are lots of levels to defragmentation of SQL Server databases. All of them are important.
The first is index defragmentation. There are lots of threads about this, so I will say no more here except that it is very important.
The next is NTFS defragmentation. This is important to do regardless of if the storage is on RAID or non-RAID disks. Windows takes a certain amount of time to move through a fragmentation chain in NTFS, so bad NTFS fragmentation will harm performance.
Windows also takes a certain amount of time to build the disk I-O commands, so if it can build a command to process a number of contiguous NTFS sectors this will take less time than building a series of commands to process non-contiguous sectors.
Depending on how your storage system works, a NTFS defrag may not defrag how the data is actually laid out on the disks. If your storage system has a large enough cache, the actual layout of data on the disks is not important.
If performance to a SAN is hurting you, then you need to engage the SAN administrator to help sort it out. There are many items (HBA configuration, SAN switch configuration, co-location of active data, SAN cache size, etc) that can affect how quickly a SAN serves data to SQL. Although the SAN cache may be the problem, other items are often the actual cause of poor SAN performance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Scott Duncan (5/20/2008)
I can see how defragmenting works on a single physical drive (after all, that's where it all started).I've always wondered though - how does it handle drive arrays? Windows (and by extension, Diskeeper et al) may see a single 400GB drive, but if the data is striped across 5 drives, how is defragging going to help? What's to say that by running a defrag tool, you aren't just moving data from one random area of each disk to another random area of each disk? Given that Windows is only seeing what the RAID controller is telling it is there (e.g., a single disk instead of 5), what's to stop the RAID controller + disk just firing the data onto whatever cluster it sees fit?
Even in a RAID array, there is still a logical order in which the data is written across all of the disks. While the controller handles the order in which the disks are striped, it presents the "single" disk to the OS in the same cluster order as it is using on the array (disk 1 cluster 1 is cluster 1 to the OS, disk 2 cluster 1 is cluster 2 to the OS and so on). So, defragging a RAID disk is still beneficial to both systems.
Actual performance gains will vary depending upon the stripe size, number of disks and amount of cache on your RAID controller. I would suggest doing a bit of reading on the topic. There are some great articles that will help you decide how to setup your RAID array for optimal performance. Some will argue that defragging a properly setup RAID array is not necessary but I can say, from personal experience, it does make a difference.
-
I'm assuming that we all defrag our PC's on a regular basis and that we genrally see an improvement if we do so?
Same for servers.
It's the actual file which fragments, and it's making the files contiguous which is important. you get far more sequential io from a disk than random io - fragmentation decreases the likelyhood of faster , and larger, sequential io.
Aside from that severe fragmentation can also fragment the mft and that's really bad news.
raid. san, single disk - all the same.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply