mdf fragmentation

Hi there, I hadn't realised that MDF files could become fragmented (I already check for fragmented log files). Is there an easy way to check for a fragmented MDF and what impact does it have on performance? Thanks

more ▼

asked Feb 23, 2011 at 02:46 AM in Default

avatar image

195 9 8 11

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Defragging files on the hard disk is a task best left to the experts - Diskeeper, Defraggler, etc.

Defragging database info internally? Then you're looking at clustered index rebuilds.

more ▼

answered Feb 23, 2011 at 03:26 AM

avatar image

ThomasRushton ♦♦
42.4k 20 60 54

Got it - becasue the clustered index "is" the table, as long as I'm defragging the clsuter index I should be OK.

Feb 23, 2011 at 04:24 AM JohnStaffordDBA

Right. You might want to make sure that you specify the "ALL" option in your ALTER INDEX statement to make sure that you get the non-clustered indexes as well (but be aware that that will fail if an index is unavailable for some reason.)

See http://technet.microsoft.com/en-us/library/ms188388.aspx for the full story...

Feb 23, 2011 at 04:28 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

You would need to look at file fragmentation in the filesystem if you are meaning the actual mdf. Windows Defrag or similar tools can do an analysis of a drive and show what it considers to be fragmented files.

To actually defrag the files you would need the DB to be detached, or use a tool that can do online defragging (I have used diskkeeper in the past on systems that weren't being hit hard on the SQL side).

Be aware that this can have negative impact on system performance whilst you perform the defrag. I am not too sure if you will have many benefits though. I would think internal fragmentation would be a worse cause for slow performance, but that is just a feeling.

more ▼

answered Feb 23, 2011 at 02:57 AM

avatar image

26.2k 18 38 48

(comments are locked)
10|1200 characters needed characters left

what sort of fragmentation are you referring to? There is file on disk fragmentation - as you get with any file that windows defragmenter or a 3rd party tool will resolve() and* there is the fragmentation of objects within the datbase file. This is resolved from within SQL Server.

more ▼

answered Feb 23, 2011 at 02:59 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

I was aware that the actual file could get fragmented on disk, but is hard to resolve as I'm not allowed to take the database offline to run a full disk defrag. I was more concerned about comments on various posts/forums suggesting that the MDF can become logically fragmented internally - do I need to worry about this?

Feb 23, 2011 at 03:06 AM JohnStaffordDBA

yes and no! Dont worry about it but certainly plan for it and takes steps to limit it. For external frag I use PerfectDisk which has an agent to access the database files without taking the database offline. I use it to work on the files overnight/out of hours so system perf not affected.

Feb 23, 2011 at 04:39 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 23, 2011 at 02:46 AM

Seen: 2044 times

Last Updated: Feb 23, 2011 at 02:46 AM

Copyright 2018 Redgate Software. Privacy Policy