8

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.

Improve this question
0

2 Answers 2

Reset to default
7

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.

Improve this answer
0
-4

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.

Improve this answer
2
  • 2
    Absolutely incorrect. At least on recent versions of Windows. As in Windows Server 2003+, or Windows XP+
    – Hannah Vernon ♦
    Commented Mar 11, 2013 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.
    – Hannah Vernon ♦
    Commented Mar 11, 2013 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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