August 23, 2013 at 9:50 am
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
August 23, 2013 at 11:32 am
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.
August 23, 2013 at 11:38 am
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
August 23, 2013 at 12:05 pm
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?
August 23, 2013 at 12:15 pm
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
August 23, 2013 at 12:55 pm
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 23, 2013 at 1:08 pm
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.
August 23, 2013 at 3:44 pm
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. 😀 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
Change is inevitable... Change for the better is not.
August 24, 2013 at 12:25 pm
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
August 25, 2013 at 10:24 am
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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 27, 2013 at 2:44 am
From experience steer well clear of trying to manually defrag disks on a SAN use only recommended utilities. A single .MDF or .LDF can span a large number of disks and trying to defrag the file by manually defraging the disks it is on can cause lots of issues including corruption of the files.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply