SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A question on defragging the *physical* files (mdf / ldf)


A question on defragging the *physical* files (mdf / ldf)

Author
Message
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4079 Visits: 11749
So, I'm looking at possibly setting up a job in Windows (although I may have to manually trigger it) to defrag the disks in my servers. For the old-hands though, who've probably dealt with similar situations, I've got a couple questions, and haven't found satisfactory answers yet...

1. Will the built-in Windows Defrag (Server 2008 / 2008 R2) defrag the MDF / LDF files without me stopping SQL Server?
2. Is it even worth the effort to defrag considering all my servers are virtual (VMWare) and the VMWare storage is all on SAN?

I'm inclined to think the answer to #2 will render the answer to #1 irrelevant. I just ran the "Analyze disk" against one of my QA boxes data volumes, and it reported only 2% fragmentation, so I suspect that thanks to being virtual, there isn't really much fragmentation...

Thanks,
Jason
dan-572483
dan-572483
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1333 Visits: 1999
We've had this debate at my workplace. My view is that defrag utilities from Microsoft & others that run from within Windows assume they are viewing physical disks. But they are not. There's no relation to what the defrag software sees as the file allocation on the logical disks and the actual layout on the physical disks. So running a defrag operation on virtual disk hosted on a SAN is just going to generate a bunch of unneeded I/O and is just as likely to increase read/write times as it is to decrease them.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87621 Visits: 45272
Check with your SAN admin as to whether the SAN has any defrag software. If it does, use that if you have to. Some SANs intentionally fragment files for performance reasons.
If you are going to defrag, stop the SQL service. Yes, some tools say they can defrag online, one error and you have corrupt databases.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 1930
jasona.work (8/23/2013)
So, I'm looking at possibly setting up a job in Windows (although I may have to manually trigger it) to defrag the disks in my servers. For the old-hands though, who've probably dealt with similar situations, I've got a couple questions, and haven't found satisfactory answers yet...

1. Will the built-in Windows Defrag (Server 2008 / 2008 R2) defrag the MDF / LDF files without me stopping SQL Server?
2. Is it even worth the effort to defrag considering all my servers are virtual (VMWare) and the VMWare storage is all on SAN?

I'm inclined to think the answer to #2 will render the answer to #1 irrelevant. I just ran the "Analyze disk" against one of my QA boxes data volumes, and it reported only 2% fragmentation, so I suspect that thanks to being virtual, there isn't really much fragmentation...

Thanks,
Jason


I would stay away of defrag the SAN with the built-in Windows Defrag, specially if your SQL boxes are virtual, and you do not have physical mdf or ldf files there. But, if you do it, expect several hours of slow and intensive disk operations, depending of how big your LUN is. And I would shutdown everything that is using that LUN anyway.

Fragmentation on virtual machines is different than on a real machine. If you are using VMware, I would use the VMware defrag tools instead. VMware workstation provides that capability. I would be surprised if VMware Server or VSphere does not.


May I ask you a question? why so interested on defrag the actual LUNs? Do you have any evidence that is causing a problem?
jasona.work
jasona.work
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4079 Visits: 11749
sql-lover (8/23/2013)
I would stay away of defrag the SAN with the built-in Windows Defrag, specially if your SQL boxes are virtual, and you do not have physical mdf or ldf files there. But, if you do it, expect several hours of slow and intensive disk operations, depending of how big your LUN is. And I would shutdown everything that is using that LUN anyway.

Fragmentation on virtual machines is different than on a real machine. If you are using VMware, I would use the VMware defrag tools instead. VMware workstation provides that capability. I would be surprised if VMware Server or VSphere does not.


May I ask you a question? why so interested on defrag the actual LUNs? Do you have any evidence that is causing a problem?


Mostly just gathering information, and I recalled a posting somewhere that one should keep an eye on both index fragmentation (caused by inserts / deletes) and the physical file fragmentation. At the moment, there's no problems, and after seeing what Win reported as the fragmentation, I'm even less worried about this and less likely to pursue any sort of defragmenting of the disks / files.

Now, the index fragmentation, that I'm going to keep an eye on...

Thanks all!
Jason
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7733 Visits: 9971
Since NTFS still fragments the file - which has nothing to do with how that file is laid out on a SAN - you should still look at the fragmentation level of the file and defrag the file if you have a lot of file fragments.

Each file fragment will generate a split I/O to the SAN which can affect overall performance.

Take for instance a database file or log file that was created at the default sizes, with the default auto growth settings. For a data file, the default is now 1MB and for log files it is 10%. Every time SQL Server needs to grow the files - a new file fragment could be created.

If your data file is now 10GB - and it grew out to that size in 1MB increments - it is possible that you have thousands of file fragments. This will increase the number of split I/Os to read that data from the SAN volume - and could seriously affect your performance.

I would definitely review the files and defrag them if you have a lot - and as Gail mentioned, definitely do this with SQL Server shut down to be sure you avoid any possible corruption of the files.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

sql-lover
sql-lover
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 1930
I am sorry to disagree... but for a SAN with actual mdf and ldf files there, I see no reason to defrag, specially if we are talking about TBs of data. I know NTFS creates fragmentation and a SAN can be fragmented, that's true, I just believe there are other ways to fix or improve that, for example, picking the right RAID or improving the actual disk's pool or LUN design.

This is a very controversial topic, but most modern SANs use a different technology and the actual file is all over the place using very complex algorithms, and let's not even talk about automated tier systems SANs. So the benefit of running a defrag is minimum.

Having said that, I am aware that most SAN systems provide such defrag tools. So I would rely on my SAN admin or a SAN expert and ask him if he really recommends such task and if I will get a benefit or not.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86422 Visits: 41098
sql-lover (8/23/2013)
I am sorry to disagree... but for a SAN with actual mdf and ldf files there, I see no reason to defrag, specially if we are talking about TBs of data. I know NTFS creates fragmentation and a SAN can be fragmented, that's true, I just believe there are other ways to fix or improve that, for example, picking the right RAID or improving the actual disk's pool or LUN design.

This is a very controversial topic, but most modern SANs use a different technology and the actual file is all over the place using very complex algorithms, and let's not even talk about automated tier systems SANs. So the benefit of running a defrag is minimum.

Having said that, I am aware that most SAN systems provide such defrag tools. So I would rely on my SAN admin or a SAN expert and ask him if he really recommends such task and if I will get a benefit or not.



Then I agree to disagree. :-D I've actually had good luck with improving performance, especially for batch jobs, by using products like DiskKeeper to defrag the SAN (specifically, MDF and LDF files) especially with today's rather large individual disks and especially since a lot of people made the mistake of using the default initial size and growth settings on the birth of the databases. Defragmentation was less important in the past when you had many more disks per TB because there were many more sets of R/W heads in play per TB. That's not true so much today.

And, don't forget... behind every VM disk, there's hardware that can contain fragmentation and it's going to matter especially if the fragmentation is serious.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLCharger
SQLCharger
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 1422
I use contig from sysinternals for defragging files.

Run it with -a -s options to see how many fragments you have per file within your sql data folder.
If you see that an important mdf is spread across numerous files, you may wish to defrag it using the -v option.
If you rely on sequential access for speed, it may be worth investigating further.

I've had success with it with attached files during quiet hours, but maybe I was just lucky.Ermm
I would suggest going for a window during which SQL Server would be stopped - it is best to be safe than sorry;-)

Best of course is to pre-allocate space and avoid file-level fragmentation in the fist place!

Cheers,

JohnA

MCM: SQL2008
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7733 Visits: 9971
sql-lover (8/23/2013)
I am sorry to disagree... but for a SAN with actual mdf and ldf files there, I see no reason to defrag, specially if we are talking about TBs of data. I know NTFS creates fragmentation and a SAN can be fragmented, that's true, I just believe there are other ways to fix or improve that, for example, picking the right RAID or improving the actual disk's pool or LUN design.

This is a very controversial topic, but most modern SANs use a different technology and the actual file is all over the place using very complex algorithms, and let's not even talk about automated tier systems SANs. So the benefit of running a defrag is minimum.

Having said that, I am aware that most SAN systems provide such defrag tools. So I would rely on my SAN admin or a SAN expert and ask him if he really recommends such task and if I will get a benefit or not.



Well - we can agree to disagree. With that said, the SAN has no idea about the files or even the file system. On the SAN you create a volume and mount that volume on a server. The volume could be mounted to a Unix, VMS, Windows, Linux - or other OS system.

The file system can be NTFS, FAT, FAT32, NSF or other file system.

The SAN has no idea what happens on the OS or with those files. Don't trust me - open perfmon and check out your split I/O's. If you have a file with a lot of file fragments you will see a lot of split I/O's occurring which can only mean you are experiencing a performance hit.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search