Your experience and advice: Disk Defrag tools

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Your experience and advice: Disk Defrag tools

Author

Topic

Zer0n3
Starting Member

6 Posts

Posted - 2010-03-24 : 16:24:18

Hi All,

We at my company are revisiting the way our disk defragmentation tools are setup and scheduled. We have run into an issue where an application was offline during a critical period and it happened to be during a Smartplacement maintenance period. Currently, the schedule is one pushed by AD through an OU and is set the same for every server regardless of hosted services on that server.

We have a mix of 2008, 2005 and 2000 servers that are impacted by this. Picked this forum for the level of visibility.

So what I am really looking for from your pool of expertise is:
1) Do you use Smartplacement against SQL server while it is online? Have you had problems with this?

2) Do you think that it's safer to use "Defragment Only" for online databases?

3) If you do defragment any/some of your database files online, do you think it is advisable to create custom schedules per server unique to its window of operation?

4) Any other advice or tips?

Thank you all for your time and help!

Kristen
Test

22859 Posts

Posted - 2010-03-24 : 17:24:25

In general SQL databases should not grow - they should be pre-allocated space, and only grow if they happen to exceed that space - and then they should grow by enough space to last them for "a while".

All those things are variable, of course, but in the real world that should mean that

a) the main part of the file does not get more/less fragmented
b) Additional extensions to the file are not very common

thus I don't see any point in scheduled de-fragmentation - particularly as most of the tools I have seen move files just to shuffle them nearer to the start of the disk - i.e. move an already contiguous file, bit by bit, just to force the whole file to be in a different place.

We use CONTIG.EXE from Sysinternals (now owned by Microsoft) to defrag individual database files, and we only do it during scheduled downtime maintenance.

Zer0n3
Starting Member

6 Posts

Posted - 2010-03-25 : 11:04:24

Hi Kristen, thanks for your reply.

I agree on the DB growth except we have an issue in many cases where we currently have no archival/purge for some systems. In some cases I have no other choice but to backup the data and log files to either the same area as the data or log files. Not a good situation but comparing where this company was before I started and now, well this is just another battle against the status quo.

I am currently in a situation where my servers suffer under this automatic Smartplacement (disk re-arrange and making files contiguous) and we have seen this type of disk maintenance cause unresponsiveness during critical windows of operation. I am trying to convince the company to stop this for the database servers.

More feedback please! I want to show my company the way other companies handle this situation.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-25 : 13:25:17

we very rarely defrag physical drives. when we do, we use OS defrag and run it manually.

there is not much performance to be gained by doing so. in fact, unless your indexes and query plans are in tip top order, it's generally a waste of time to defrag physical files.

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 13:45:27

Why don't you just get Smartplacement to ignore *.MDF and *.LDF?

I wouldn't have much respect for an IT department that uses a one-size-fits-all solution with no understanding of how company-critical databases need to be treated

Zer0n3
Starting Member

6 Posts

Posted - 2010-03-25 : 14:00:45

quote:
Originally posted by Kristen

Why don't you just get Smartplacement to ignore *.MDF and *.LDF?

I wouldn't have much respect for an IT department that uses a one-size-fits-all solution with no understanding of how company-critical databases need to be treated

You hit the nail right on the head Kristen!

I didn't want to put the words into anyone's mouth but you confirmed what I've felt as well. We are considering pushing applying a filter to *.ndf,*.mdf and *.ldf but like with our Anti-virus, we had to fight to get those filters put in.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-25 : 14:44:57

quote:
Originally posted by Zer0n3

quote:
Originally posted by Kristen

Why don't you just get Smartplacement to ignore *.MDF and *.LDF?

I wouldn't have much respect for an IT department that uses a one-size-fits-all solution with no understanding of how company-critical databases need to be treated

You hit the nail right on the head Kristen!

I didn't want to put the words into anyone's mouth but you confirmed what I've felt as well. We are considering pushing applying a filter to *.ndf,*.mdf and *.ldf but like with our Anti-virus, we had to fight to get those filters put in.

Sounds like people with no real knowledge of the impact of what they are doing are making this decision.

SQL Server is not going to benefit very much from defragmentation of the physical files, unless they are severely fragmented.

CODO ERGO SUM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-25 : 14:54:25

^^ What he said.

I don't know anything about Smartplacement, but I do know that moving around the .mdf/.ldf/.ndf files with the service running (if even possible) is a very bad idea. VMWare often crashes SQL Server when it tries.

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 15:00:49

I think you regulars may have better maintained databases that the average punter - a reasonable initial size, and not many extensions beyond that.

I expect many databases are set to start at 1MB and increase by 10% - resulting in 100's / 1,000's of fragments and extensions as a consequence.

For those I think that a defrag to make them contiguous (but not necessarily at the start of the disk) is a Good Thing

Zer0n3
Starting Member

6 Posts

Posted - 2010-03-25 : 15:14:38

Thank you all for your feedback and experience.

Most of the fragmentation comes from other files (various logs, tran log backups and full backups) on the disks and not so much the actual data files.

But the fragmentation of one thing trips maintenance to run for the whole drive. Adding the extension filter mentioned above would be a great start at least but it is pretty pointless to de-fragment a transaction log backup that will be deleted automatically in 24-48 hours.

I guess I could push to filter out *.trn, *.bak and *.hbc as well and then it will try to run but most everything on the disk will be filtered out anyway.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-03-25 : 15:31:40

quote:
Originally posted by Kristen

I think you regulars may have better maintained databases that the average punter - a reasonable initial size, and not many extensions beyond that.

I expect many databases are set to start at 1MB and increase by 10% - resulting in 100's / 1,000's of fragments and extensions as a consequence.

For those I think that a defrag to make them contiguous (but not necessarily at the start of the disk) is a Good Thing

I don't disagree that severe file fragmentation can be a problem, but a one time manual defrag might be better than an online defrag.

Another serious concern in that situation is the internal fragmentaion of the log file into many virtual log files (VLF). I believe the usual recomendation is 50 VLFs less. I have seen many cases where the log had 2000 to 6000 VLFs. When I see that, I do something like the steps described in Kasim Wirama's blog entry below.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx

http://netindonesia.net/blogs/kasim.wirama/archive/2009/11/02/internal-fragmentation-on-sql-server-transaction-log.aspx

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 16:39:39

" a one time manual defrag might be better than an online defrag"

Absolutely agree. We only ever do it during scheduled maintenance (i.e. database offline, which also probably gives the defrag tool a clearer run at the job)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-25 : 16:46:52

We use CONTIG.EXE from Sysinternals (now Microsoft) to defrag some of our database file, online.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-03-25 : 16:48:53

quote:
Originally posted by Michael Valentine Jones

I have seen many cases where the log had 2000 to 6000 VLFs-

I recently had a problem with massive VLF growth.
Even fixing the VLF's, the day after they were back at some 194,000 again.
After som tracing and investigating, I found Backup Exec (from Symantec) to be the villain.
http://weblogs.sqlteam.com/peterl/archive/2009/12/09/Problem-with-SQL-Server-service-restart.aspx

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-03-25 : 17:36:14

You'll be thrown out for swearing