Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A question on defragging the *physical* files (mdf / ldf) Expand / Collapse
Author
Message
Posted Friday, August 23, 2013 9:50 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 737, Visits: 5,460
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
Post #1487901
Posted Friday, August 23, 2013 11:32 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 557, Visits: 1,637
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.
Post #1487956
Posted Friday, August 23, 2013 11:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:35 PM
Points: 40,438, Visits: 36,895
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 2008, MVP
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

Post #1487958
Posted Friday, August 23, 2013 12:05 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 416, Visits: 1,336
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?
Post #1487967
Posted Friday, August 23, 2013 12:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 12:17 PM
Points: 737, Visits: 5,460
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
Post #1487969
Posted Friday, August 23, 2013 12:55 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 4,363, Visits: 9,545
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1487986
Posted Friday, August 23, 2013 1:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 416, Visits: 1,336
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.

Post #1487991
Posted Friday, August 23, 2013 3:44 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:34 PM
Points: 35,609, Visits: 32,200
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1488042
Posted Saturday, August 24, 2013 12:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
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.
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
Post #1488141
Posted Sunday, August 25, 2013 10:24 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 3:14 PM
Points: 4,363, Visits: 9,545
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1488210
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse