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

JohnStaffordDBA gravatar image

195 8 8 9

(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

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

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

WilliamD gravatar image

25.9k 17 19 41

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1751 times

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