February 4, 2009 at 9:19 pm
We have a client with a sysadmin who was attempting to use the Windows defrag utility on the physical SQL Server files because they were 85% fragmented. He contacted us because he couldn't get it to work and wanted to be proactive about protecting himself from a hard drive failure. We basically told him to create a backup, test the restore, and if he could afford the downtime, go ahead and shutdown SQL Server and run the defrag utility. I did some Google research and apparently it's perfectly safe. My questions are as follows:
1) Is it true that heavily fragmented files on a disk are more likely to cause a disk crash?
2) Do the DBAs here typically defrag their physical SQL files? And if so, what is the reasoning?
February 5, 2009 at 1:21 am
No.
No.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 5, 2009 at 7:38 pm
lol...thank you.
February 5, 2009 at 7:53 pm
fragmented MDF files happen in situations where the file gets shrunk, or needs to autogrows to hold more data.
ideally, you pick an initial size of a database so that the MDF is on a huge contiguous chunk of disk. When it's sized with plenty of room for growth, there is no need to defrag ment at the operating system/disk level.
Same goes for the log files...you might see lots of posts where people say they want to recover the space being eaten up by the log....but if your log expands, and then you shrink it, adn it has to try to get more space again...that can fragment your files. While fragmented files do lead to longer seek times in disk I/O, it's not something you need to consider doing on a weekly basis or anything...
i personally use Contig.exe from SysInternals, so I can command line it to do just specific files.
Lowell
February 5, 2009 at 9:58 pm
Windows Server 2003 supports deframentation of open files, so utilities like DiskKeeper can defragment them while SQL Server is running.
I'm not sure if you would see much improvement in performance.
February 6, 2009 at 2:57 am
Badly fragmented disk files can cause performance issues in SQL Server. The impact will depend a lot on the underlying disk subsystem.
If your Windows disks map to storage that is allocated track by track (some SAN vendors do this), then Windows fragmentation will mainly affect the Windows layer. It will take Windows a bit longer (CPU time) to work out what disk tracks SQL is asking for and pass this to the disk subsystem.
If your Windows disks map to contiguous disk space (striped or otherwise), then the impact will be greater. You will have the same Windows impact as above, but at the disk layer you will not have a physical sequential read. As disk subsystems are optimised around how space is allocated, losing the ability to physically do a sequential read is not something you want to do.
For very bad fragmentation there can be an additional impact on SQL Server when database files grow. When a file grows, NTFS has to take a lock on its schema, so it can transfer some free space to the file. If there are very long allocation chains describing the file and long chains describing the free space, it will take longer for NTFS do grow the file and return to SQL Server. During this time your user query does nothing.
So there are advantages in doing a Windows defrag, but they should be scheduled outside of peak times, as they will impact the speed of Windows (and therefore SQL Server) while they run.
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
February 6, 2009 at 8:34 am
I use the Windows defrag utility on our SQL Servers. I will run it before I install SQL Server as well because Windows and all of the software that gets installed to get started fragements the HD. After I install I defrag as well. I typically defrag our Prod SQL Servers once a year in a non peak time... Thanksgiving, Christmas, New Years night etc on our more heavy useage ones. The one 80 gig db the mdf file is in 240 extents and it will not defrag even though it is on WIn2003. I probably need to shutdown SQL Server for it to fully defragment it. We cannot afford that kind of downtime since it is used by our stores 24 X 7 and I cannot prove that is is hampering usage or a defrag will help so I just leave that one alone.
February 6, 2009 at 9:59 am
We are also working on a solution to defrag our database servers. We have been looking at Diskeeper and have downloaded their free trial version. It works well on our test environment and you can defrag when the database is running. It is not very expensive either.
February 12, 2009 at 2:57 am
I think disk fragmentation is bad, and should be fixed. SQL Server files are just files like any other and when these are in many pieces there will inevitably be an impact.
Many DBAs do not have the luxury of one physical disk per file and many cannot preallocate all the space a database may require up front. Of course, you only need two files that grow and interleaving may well result. A lot of people have much more than that, and if you have other files on the same physical drive, gragmentation is inevitable and the impact is even greater.
I have seen many production environments seriously suffering from this problem in lots of different companies.
WARNING: if you are considering Diskeeper Server Edition 2009 note it can cause SQL to crash. Diskeeper 2008 Server Edition works fine.
Further details on
http://www.sqlservercentral.com/Forums/Topic305986-92-2.aspx
.
February 13, 2009 at 8:52 am
I run contig.exe on specific databases to keep the files in one piece.
Regards,
Jim
February 23, 2009 at 2:48 am
I'm not really familiar with the locking characteristics of the underlying API calls that contig.exe uses (nor how much data it tries to move at a time).
Can you run it whilst SQL Server is in flight, or does this cause issues?
Thanks
.
February 23, 2009 at 5:12 am
I run contig.exe online, and have had no issues
Regards,
Jim
February 23, 2009 at 6:48 am
jmoss111 (2/23/2009)
I run contig.exe online, and have had no issuesRegards,
Jim
What is "contig.exe" and does it come with Windows or SQL Server for free?
Also, on a crowded disk, will it move files to make room for a single, contiguous MDF or LDF like DiskKeeper or SpeedDisk will?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 23, 2009 at 6:58 am
Google tells me that it is free with Sysinternals tools.
It runs a NTFS defrag on named files only.
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
February 23, 2009 at 7:03 am
Its free...
http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx
There is also a tool called DiskView.exe which visualises the fragmentation.
I just did a smallish test defragmenting a 5GB MDF in a dozen pieces on a highly fragmented drive, and it didn't do anything, saying that the file could not be defragmented, so I suspect the answer to your second question is no. It did a much better job wth smaller files.
Looks like a useful tool with some limitations. Thanks for the tip jmoss111!
.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply