0

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?

Glorfindel
  • 1,213
  • 4
  • 15
  • 22
Martin Smith
  • 322
  • 1
  • 12

2 Answers2

1

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.

Sergey
  • 2,121
  • 15
  • 14
  • 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 02 '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 02 '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 02 '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 02 '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 02 '11 at 15:11
  • MSDN blogs it's not a blogging platform for MS staff only. Also it's allow to create 'product blog' for partners staff (developers/admins) who create applications/services using MS technologies. And blog you refereed to it's just the case - Kronos Workforce Central it's workforce management and administration suite. Anyway 'trust [no one]but verify' it's a great rule about info from Internet neither it comes from any 'trusted'(MS employee/MVP) source nor from any other source... – Sergey Feb 02 '11 at 15:41
0

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
gbn
  • 6,079
  • 1
  • 18
  • 21
  • 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 02 '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 02 '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 02 '11 at 14:39
  • @squillman: Any comments on the article itself? Judging by this it's even worse than a DBA tool http://blogs.technet.com/b/sql_server_isv/archive/2010/11/12/non-default-database-settings-for-sql-server-with-kronos-workforce-central-suite.aspx – gbn Feb 02 '11 at 14:50