Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I was just reading this article where the author talks about the file system fragmentation that can be caused by growing database files. There was one bit that I didn't quite follow.

What about Windows defragmentation tools?

Although you can use a Windows defragmentation tool to defragment your database files, these tools simply move chunks of files around to get them contiguous. This moving of chunks of files can cause internal fragmentation that you might not be able to resolve easily.

Is the author saying here that the disc defragmenter makes no attempt to put the chunks of files in the correct sequence or have I misunderstood? If he is saying that then is this a limitation of all disc defragmenter utilities - even commercial ones?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

Luckily, you can resolve both internal table extent fragmentation and page fragmentation by rebuilding your indexes or by building and dropping a clustered index on a heap table.

  1. You should not have heaps
  2. You should be rebuilding your indexes regularly anyway.

To resolve table fragmentation, you often have to rebuild the table or clustered index in a different filegroup.

Not everyone has a spare filegroup.

[cynical]

Seems to me a pitch to use "Kronos Workforce" as a magic way to do stuff your DBAs should be doing anyway. I've had the same issues with vendors pitching some whizzy (expensive) product to my management to do standard DBA tasks

[/cynical]

Edit: It appears I've misunderstood. Kronos is not a DBA tool.

However, the FUD in the article could be explained by these "tweaks"

  • Changing the max degree of parallellism (MAXDOP) to 1
  • Set the default Index Fill Factor to 75 percent
  • Disable the Auto Update Statistics Setting
share|improve this answer
    
Ah OK the cynical angle hadn't occurred to me. Does the claim that Windows defragmentation can cause internal fragmentation make any sense to you? –  Martin Smith Feb 2 '11 at 12:36
    
@Martin: I suspect only temporary effect until a rebuild index. Also, your working data should be in RAM anyway, any you'd have fewer sustained disk reads/read-aheads than the article seems to imply. –  gbn Feb 2 '11 at 12:41
    
-1 Workforce Central is an app that runs on top of SQL server, not a dba tool. The blog post is just suggesting basic dba tasks to help Workforce run better. –  squillman Feb 2 '11 at 14:39
    
@squillman: Any comments on the article itself? Judging by this it's even worse than a DBA tool blogs.technet.com/b/sql_server_isv/archive/2010/11/12/… –  gbn Feb 2 '11 at 14:50

Any external fragmentation tool (doesn't matter commercial or free) does not restructure the internal contents of the file, unlike DBCC REINDEX.

After defragmentation, the defragmented file will be a bit for bit duplicate of the original. Therefore, any holes within the database are still present and you will still need, from time to time, to rebuild your indexes to combat internal fragmentation.

share|improve this answer
    
I understand why the Windows disc defragmenter doesn't fix internal fragmentation. My question was as to how it could actually cause it? –  Martin Smith Feb 2 '11 at 12:31
    
Internal fragmentation occurs when records are removed from database pages but the space it occupied is still there after deletion. Eventually this space is reused, but as it is reused, the data pages become fragmented. –  Sergey Feb 2 '11 at 13:15
    
@Sergey - Again this seems completely orthogonal to the issue of Windows defragmentation which was my point of confusion. Unless the author is using "internal fragmentation" to mean something different from what I (and you I think) understand this concept to mean the two things seem entirely unrelated. And if the author is referring to something different what is the issue they are referring to? –  Martin Smith Feb 2 '11 at 14:40
    
Oh, yes. You're right. Internal and external fragmentations in 'classic interpretation' are unrelated things. But is it first time you've found an erroneous info in Internet? –  Sergey Feb 2 '11 at 15:02
    
@Sergey - No it wouldn't be the first time I've read something online that contains errors but as it is on an MSDN blog I assumed that there must be something in what they were saying and that I just wasn't getting it. Having looked at gbn's update I am now somewhat doubting the credibility of this blog however! –  Martin Smith Feb 2 '11 at 15:11

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.