September 23, 2009 at 11:30 am
A a number of SQL Servers, OS admins have complained of file system fragmentation. My guess is this is likely due to database backups to disk, and subseqenty deleting of old ones.... both transaction log backups and full backups.
Anyone have strategies to deal with this?
September 23, 2009 at 3:16 pm
You can defrag the drives.
'strategies' for minimizing the need to do this is really just good planning of the disk layout and space allocation. Put your backups on a separate disk array. Allocate sufficient space in the file groups when you create your DBs so the files will have room for internal growth.
As far as cleaning up the existing fragmentation, you can detach the database(s) and move the files to a different array, then delete them on the original array. Copy them back and re-attach. I would also do a backup before you do this so in case something happens you can restore it from that.
The probability of survival is inversely proportional to the angle of arrival.
September 24, 2009 at 2:10 am
sturner (9/23/2009)
You can defrag the drives.'strategies' for minimizing the need to do this is really just good planning of the disk layout and space allocation. Put your backups on a separate disk array. Allocate sufficient space in the file groups when you create your DBs so the files will have room for internal growth.
As far as cleaning up the existing fragmentation, you can detach the database(s) and move the files to a different array, then delete them on the original array. Copy them back and re-attach. I would also do a backup before you do this so in case something happens you can restore it from that.
Good suggestions, all depends on one, how much space you have to play with and two, how big is your maintenance window. Some people schedule downtime for sql and do it then, preferably when patching is going on.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 24, 2009 at 6:46 am
You could also use Diskeeper (and others) to defrag files whilst in use...
September 24, 2009 at 6:58 am
Diskeeper is what we have been using, but it seems to "lock files" from time to time. The result is that my backup job is unable to delete an old file... which then means total utilization on the disk shoots way up.
September 24, 2009 at 8:47 am
Are you sure it's not tape backup (or SQL) agent locking the files? They tend to do that from time to time.
Diskeeper probably works in a similar way to the open file backup agents- creates a temp copy of the MDF/LDF, defrags that and then switches the files (layman's explanation)
September 24, 2009 at 9:35 am
Tape backup does it too, but I've scheduled around it. I definitely identified Diskeeper as the offender- using procexp to identifiy it, then watched it disappear as soon as I stopped the diskeeper service.
September 25, 2009 at 6:06 am
That begs the questions. Are there any effective *online* disk defragmentation programs that actually work for SQL Server database volumes? Perhaps a newer version of Diskeeper?
September 25, 2009 at 6:07 am
IMHO if you have a disk dedicated to backups you do not need to worry about fragmentation on this drive. Because backup files get created and deleted on a daily basis, this drive will quickly get disk fragmentation, unless you continuously run a disk defrag. You almost certainly have better things to do with the CPU and I-O a disk defrag of your backup drive will take.
Disk fragmentation on a drive holding database files is a different matter. Bad fragmentation will definitely affect database performance. Although you should defrag drives holding database files, there is probably no need to have a defrag continuously running in the background. This is because you then have no control about when it will move some of your data, and it could take resources to do a maintenance procedure (which is not time-sensitive) at a time of peak business activity. A defrag of database drives is important, but should be scheduled to run in a quiet time.
BTW, just about all defrag tools do NOT work on the basis of taking a copy of a complete file and swapping it for the libve file after it is defragged. They work by moving individual fragments of a file to a new location and then updating the NTFS pointers for the file. NTFS is in effect a database, with its own locking mechanism. This allows defrag tools to put an X-lock on a fragment of a file while it is being moved. The X-lock on the fragment allows other parts of the file to be written to while the defrag moves the fragment, and forces any write operation to the fragment to be queued until the move operation is complete.
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
September 25, 2009 at 10:19 am
Thanks for the correction Ed- that's why I'm a DBA and not a windows admin 😉
ps. I have heard conflicting stories from Microsoft engineers regarding fragmentation of database files. We used to defragment quarterly (with SQL stopped), however some engineers have mentioned that in their testing SQL performance is negligibly affected by file fragmentation (only index fragmentation)....
September 25, 2009 at 10:49 am
Thanks all for the valueable insights. I tend to agree with Ed regarding backup partitions- why waste the resources when were deleteing and recreating daily.
David- I dont think I understand how file fragmentation could NOT affect performance. If sql server has to look all over the disk for a sequential read, certiainly this must be a performance bottleneck.
September 27, 2009 at 11:09 am
I agree with you Warren, but not many Windows admins defrag servers anyway (from my experience). They suggest a rebuild and restore from tape- if anything. All defrags (database or otherwise) tend to be initiated by DBAs from what I have seen...
September 28, 2009 at 3:01 am
Disk fragmentation as seen by Windows is nowadays very rarely the disk fragmentation seen by the disk controller.
Some SANs have the concept that a LUN is a contiguous chunk of physical disk space, striped over all the physical disks that the LUN is defined on. In this situation read, write and update work in much the same way as they would on locally attached storage. However, if the LUN is mapped over a large number of disks (some SAN admins will map a LUN across 100 disks or more), then the term 'contiguous space' becomes almost meaningless.
Other SANS have the concept that LUNs are formed from a disk track map. This means the LUN only has mapped to it those tracks that are in use, and those tracks are only ever written as new tracks or are read. If you want to update an existing track, a new track is pulled from the free space pool, written to, and the track map updated with the old version of that track returned to the free space pool.
In both of the examples above we can have disks that are totally fragmented, but because of the SAN management software still deliver great performance. But even in this situation NTFS fragmentation can still be a problem. If you have bad NTFS fragmentation, Windows has to spend time chasing along NTFS pointer chains to find the track reference you need. As each extra fragment is formed, NTFS has to take its own schema locks while the fragment is added to the appropriate file. All of this adds some risk to the stability of your system, so even if you have a situation where physical disk fragmentation is no longer a problem,IMHO you should still try to minimise NTFS fragmentation.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply