Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented.

We scheduled a task to defragment this drive at 3:00 a.m. on a Saturday morning. The job completed after 40 minutes with no apparent errors. However, the drive remains heavily fragmented.

Should we have stopped SQL Server service(s) before defragmenting?

CONTEXT

Per requests for context: We have a Microsoft SQL Server 2005 instance (9.00.5324.00) running 32-bit Windows Server 2003 (SP2) on Dell PowerEdge 2950 hardware, circa 2007, with 4GB RAM. The PowerEdge 2950 has four 68GB drives configured as RAID-1 to create two 68GB virtual disks: (1) C (boot and OS) & D (pagefile, miscellaneous other data); and (2) E (SQL data). To my knowledge, IT staff have never defragmented any of these drives...Disk Defragmenter reports file fragmentation of 66% (C), 77% (D), and 99% (E). Performance Monitor reports the following average results: "Paging file: % usage" = ~6.8%; "SQL Server: Buffer Manager - Page life expectancy" = 20 seconds; and "PhysicalDisk: Avg. disk sec/write, drive E" = between 300 and 1,100 ms. We're due for a much-needed hardware and SQL Server upgrade in a few months time (viz., new hardware, 64-bit Windows Server 2012, 64-bit SQL Server 2012, 12GB RAM), but, due to end-user performance, want to alleviate the issue as much as possible. Thus the thinking a file defrag might help for drive E, the main SQL data drive.

As an aside, last week we pulled two failed drives and rebuilt the array...not sure that matters. We contract with another IT team to maintain the server, so we do not have direct access to the equipment...our organization just pays for services.

We can afford the downtime during regularly scheduled maintenance windows (weekly) as well as out-of-band downtime, as necessary, overnight.

share|improve this question
6  
Do you have any measurement to indicate that the physical disk fragmentation is having a significant impact? Can you afford the downtime? –  Remus Rusanu Mar 11 '13 at 19:19
2  
@RemusRusanu - how would one know if any given level of fragmentation is having a negative overall effect on the system without actually defragmenting it? Inspecting the number of split I/Os through Performance Monitor might help although it will be difficult to correlate that to activity within SQL Server. This is not meant as a confrontation, by the way - I'm really curious! –  Max Vernon Mar 11 '13 at 20:25
4  
@MaxVernon: For theory sake, I guess XPerf. One could also measure side-by-side by creating an unfragmented database on same disk and running a captured workload. However, for all practical reason, the likelihood of the prevalent performance issues being in the app vs. due to fragmentation is overwhelming. –  Remus Rusanu Mar 11 '13 at 20:34
3  
My experience tells me 99 times out of 100 (or more) I would definitely agree. Performance issues are far more likely to be as a result of some ORM doing SELECT * all over the place, or some other malpractice. –  Max Vernon Mar 11 '13 at 20:40
1  
Before you make any other changes, and certainly before attempting a defrag, you want to make sure the arrays have completely rebuilt. And a piece of advice for your new system - get as much RAM as you can afford - you want as much of your regularly accessed SQL data in memory since RAM is 1,000 times faster than the fastest disks. –  Max Vernon Mar 12 '13 at 1:46

3 Answers 3

up vote 2 down vote accepted

I personally have used Raxco PerfectDisk (not associated with the company or any of their employees in any way) to do online defrags of SQL Server LUNs. Works perfectly, if it does slow the server down a bit. I would recommend doing it during periods of lighter activity. When I say "works perfectly" I am referring to it not corrupting the volume or the SQL data files.

The built-in defragmenter does a very poor job if certain structures are fragmented on the drive. PerfectDisk shows you details about all the items that are fragmented including the NTFS allocation tables, directories, alternate file streams, etc. etc.

Does PerfectDisk defragment SQL databases? http://support.raxco.com/KB/a106/does-perfectdisk-defragment-sql-databases.aspx

See this archive copy of the Technet News Magazine regarding SQL Server and fragmentation: http://web.archive.org/web/20100803204458/http://www.microsoft.com/technet/abouttn/flash/tips/tips_083104.mspx

share|improve this answer
2  
But Remus' comment has a lot more merit than just pointing to some product. I don't believe I've ever defragmented a Windows disk that SQL was on, and have never seen a strong indication that I needed to. Windows may say a drive is fragmented but what's important is whether SQL Server is experiencing any issues because of it. In a lot of cases I suspect it really doesn't matter and there is no need to run file system-level defrags at all. You say it "works perfectly" but did you actually observe measurable improvement? Can you provide details? –  Aaron Bertrand Mar 11 '13 at 20:11
1  
I agree, Aaron, for most cases physical fragmentation on an otherwise properly engineered SQL Server will have very minimal effect. Even as in this case where the user is reporting 99% fragmentation. However, if the user has his SQL databases on the same LUN as the O/S and that happens to be on a single disk or a RAID array with a small numnber of spindles, really excessive fragmentation can have an impact. –  Max Vernon Mar 11 '13 at 20:18
1  
By "really excessive", I mean like thousands upon thousands of fragments. Think hundreds of thousands. I've seen that. Not that I'm proud or anything! –  Max Vernon Mar 11 '13 at 20:19
    
As Brent Ozar has said, if you are running your SQL Databases on a SAN where the data is shared across one giant RAID array, doing a physical defrag (and indeed a logical SQL defrag) will likely not have any measurable positive effect on the overall performance of the system. –  Max Vernon Mar 11 '13 at 20:20
    
I'm marking this as the best current answer, since it seems "Yes" if we use the right tool. Thanks! –  schultkl Mar 13 '13 at 5:01

Your problem is not disk fragmentation. Your problem is RAM and application table scans:

4GB RAM ... 68GB ... Page life expectancy 20 seconds

You need way more RAM. As in your new server should have way, way, way, way more than 12GB. Start with 64 GB, it costs basically dimes. And yes, fix your app to use indexes. 20 seconds is very clear indication of table scans that trash the buffer pool. You need to fix the app, add the required indexes and fix your queries. For your case defragmenting the drives is as much a red herring as red herrings get.

Oh, and please move the log to separate physical spindles from data. Alone.

share|improve this answer
    
Thanks Remus; if only I ruled the world.... We contract with a third-party vendor for the app, so not in our direct control, regrettably. I do appreciate your insight, thank you. –  schultkl Mar 12 '13 at 19:58
1  
More RAM and SSDs can ease your way out on a fairly slim budget. But the app needs a fixin'. –  Remus Rusanu Mar 12 '13 at 20:16
    
As a follow-up, after defragmenting the drive, performance has only marginally improved. Remus seems correct in his diagnosis of a RAM issue. –  schultkl Mar 18 '13 at 23:03
    
@RemusRusanu does it matter to try to optimize with files placed in different disks in virtual servers (vmware)? This is a general virtualization-ignorance question... –  diegohb Jan 16 at 3:25

Brad McGehee describes it well here:

http://www.bradmcgehee.com/2011/06/do-you-ever-physically-defragment-your-sql-server-mdf-ldf-files/

...if you leave the SQL Server service running during your defrag, the database files are open and therefore not defragged at all.

share|improve this answer
2  
Absolutely incorrect. At least on recent versions of Windows. As in Windows Server 2003+, or Windows XP+ –  Max Vernon Mar 11 '13 at 19:59
1  
All Windows Defrag tools use the system APIs to defrag files, and do so on a cluster level underneath the file access APIs. This is how folders can be defragged with the volume online. –  Max Vernon Mar 11 '13 at 20:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.