March 8, 2006 at 2:55 pm
Would anybody like to comment on pros and cons of using a defragmenter of live data drives? We have several SQL servers that have this. One of them is having disk I/O related problems. It has been suggested that the disk defragmantation could be the culprit, I am not necessarily convinced of this because other servers are doing the same thing and running well. Please comment.
best,
Mordechai
March 8, 2006 at 3:13 pm
When you say that you are using a defragmenter, do you mean that you have a third party tool that will defragment your MDF and LDF files? If so, I would suggest only using this tool a few times per year as fragmentation of the physical files is usually not a big performance killer. You will gain more performance benefits defraging your indexes regularly over defraging your physical files. What type of I/O problems are you having?
March 9, 2006 at 3:38 am
I have used the built-in Windows Disk Defrag tool a number of tims on our non-production boxes while SQL is dealing with user queries. I have had no problems.
Although this type of tool is supposed to be safe to use with concurrent access, I still say that anything you do is at your own risk.
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
March 9, 2006 at 7:01 am
Using disk defragment tools does help with database performance, but you should never run it during production hours on a production database. The disk I/O goes up significantly when an application like that is run. If your database does a lot of inserts and updates it would take a big hit.
March 9, 2006 at 8:12 am
If you are using the Windows defrag tool, you definately do not want to run it during business hours. For one, like Brian pointed out, you will see increased I/O, but most importantly, the windows tool cannot defrag your MDF and LDF files while SQL Server is running. As long as SQL Server is running, it locks the MDF and LDF files at the operating system level. The defrag tool cannot access the files and therefore will not actually defrag them. The Windows tool will work around all locked files so if you are running it while your SQL Server instance is on-line, you are wasting I/O and not getting the results you expect. Sure, it may defrag whatever else is on your drives, but not your database files.
March 9, 2006 at 8:50 am
It would not be so terrible if the result was only that DB files do not get defragmented while anything else wouldn't, and of course we are talking about off peak hours defragmentation. Win2003 even has a "smart" defragmenter that waits for a lull in activity. The question is - is it at all advisable to have (native) disk defragmentation run while SQL server is up (although not at its peak usage).
March 9, 2006 at 8:57 am
Personally, I would say no. As I said in my first post, you will get more bang for your buck regularly keeping on top of SQL Server fragmentation by rebuilding indexes. Physical file fragmentation is not as much of a concern as SQL Server fragmentation. While you will see some benefits from defraging your physical drives, it does not need to be done, in my opinion, more than a few times per year. With that said, I feel that a maintenance task such as this needs to be done during your maintenance window or in off-peak hours.
March 9, 2006 at 8:58 am
It won't cause a problem running it while SQL Server is up. You don't have to stop the services. But again like John said your data and log files would not be affected. You just have to get you Window of time when the server is not active or least active. We perform monthly defrags on selected drives across our 70 production servers.
April 7, 2006 at 9:17 am
I use the defragger that comes with Windows 2003 (and stop SQL). Can you recommend any other production-quality defrag tools from third parties?
TIA,
Bill
April 7, 2006 at 9:39 am
PerfectDisk by Raxco is a good one. If you have big disks (at least 1 terabyte and above) then you will notice a 20%-25% faster defragmenation time. If your drives are smaller then it is not worth the investment, and you should just stay with the OS defrag tool. The licensing for PerfectDisk is $239.95 for a single server and up to $1,599.95 for a 10 server license pack. We only use PerfectDisk on our SAN array drives.
April 7, 2006 at 12:22 pm
I have done this before but many times the included Windows driver cannot work with live databases when they are open and reports as some files are locked.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply