|
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
(comments are locked)
|
|
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. Got it - becasue the clustered index "is" the table, as long as I'm defragging the clsuter index I should be OK.
Feb 23 '11 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 '11 at 04:28 AM
ThomasRushton ♦
(comments are locked)
|
|
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.
(comments are locked)
|
|
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. 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 '11 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 '11 at 04:39 AM
Fatherjack ♦♦
(comments are locked)
|
