Thursday, April 21, 2011

Contig.exe - File Defragmenter

I'm a fan of a free tool by Piriform called Defraggler. It is a visual degragmentation tool (like the OSs used to include!) Recently I had cause to investigate fragmentation on a very overloaded drive with hundreds of thousands of text files.

Defraggler took 45 minutes to build a picture of the drive, a wait I don't want to repeat. Given my primary concern is the sql data files, I wanted to view those results first. Enter 'contig', now on Technet - formerly part of the sysinternals project.

Running Contig without parameters helpfully tells you how to use it -

D:\MSSQL\Data>contig

Contig v1.6 - Makes files contiguous
Copyright (C) 1998-2010 Mark Russinovich
Sysinternals - www.sysinternals.com

Contig is a utility that defragments a specified file or files.
Use it to optimize execution of your frequently used files.

Usage:
contig [-a] [-s] [-q] [-v] [existing file]
or contig [-f] [-q] [-v] [drive:]
or contig [-v] -n [new file] [new file length]

-a: Analyze fragmentation
-f: Analyze free space fragmentation
-q: Quiet mode
-s: Recurse subdirectories
-v: Verbose

Contig can also analyze and defragment the following NTFS metadata files:
$Mft
$LogFile
$Volume
$AttrDef
$Bitmap
$Boot
$BadClus
$Secure
$UpCase
$Extend

To view fragmentation, use the -a switch like this...

D:\MSSQL\Data>contig -a *.mdf

Contig v1.6 - Makes files contiguous
Copyright (C) 1998-2010 Mark Russinovich
Sysinternals - www.sysinternals.com

D:\MSSQL\Data\Accounting.MDF is defragmented
D:\MSSQL\Data\Accounting_UAT.MDF is in 14 fragments
D:\MSSQL\Data\AuditPC.mdf is in 7 fragments
D:\MSSQL\Data\DataStore.mdf is in 34 fragments
D:\MSSQL\Data\FakeDb.MDF is in 5 fragments
D:\MSSQL\Data\Personel.mdf is in 4 fragments
D:\MSSQL\Data\master.mdf is in 3 fragments
D:\MSSQL\Data\model.mdf is in 2 fragments
D:\MSSQL\Data\msdbdata.mdf is in 5182 fragments
D:\MSSQL\Data\northwnd.mdf is in 2 fragments
D:\MSSQL\Data\pubs.mdf is in 2 fragments
D:\MSSQL\Data\Software.mdf is in 4 fragments
D:\MSSQL\Data\Software_UAT.mdf is in 9 fragments
D:\MSSQL\Data\Telecoms.mdf is in 17 fragments
D:\MSSQL\Data\tempdb.mdf is in 42 fragments
D:\MSSQL\Data\tools.mdf is in 5 fragments
D:\MSSQL\Data\Weblogs.MDF is in 89 fragments

Summary:
Number of files processed : 17
Average fragmentation : 176.03 frags/file

Pretty obviously, the above is BAD. Fragmentation of the SQL datafiles on the drive caused by autogrowth. Oh, and the filenames have been changed in the example above for confidentiality. I wouldnt contemplate mixing all those systems in the real world :)

No comments: