SQL Server 2000 and Defrag

In the JDE docs on SQL Server tuning, it refers to the fact that as data files grow they get fragmented. So if autogrow is set on your prod database for example, at a file level the database is fragmented each time the file autogrows. So does anyone run defrag programs over these drives containing SQL datafiles? Or do people set datafiles to be much larger than expected data size (e.g. 10Gb file for expected 6Gb of data) so that the file never autogrows or maybe jsut turn off autogrow.

cheers

Antipodes :

I don't use defrag applications on SQL Server data and log partitions.
I haven't seen great performance enhancements on data and
transaction log disk partitions; though these tools were very useful
on the \b7333 shares of Deployment and Enterprise.
OneWorld packages generate more than 25000 files each and contribute
a lot to NTFS fragmentation.
I prefer to backup the whole database, format its partition, and
restore data. This way, you'll have an unfragmented partition and the
database file won't be fragmented (neither externally nor internally).
I do these reformatting once or twice a year.

Sebastian Sajaroff (JDELIST)

This approach does not eliminate the internal fragmentation of the data
within the files - the restore process simply restores the files "as is".

SQL has internal tools to fight it...

Re: RE: SQL Server 2000 and Defrag

Yes SQL does indeed deal with the internal fragmentation, but what I am specifically interested in is the issue of file fragmentation at O/S level which must happen whenever the SQL datafile autogrows. THere must be some performance hit.

RE: RE: SQL Server 2000 and Defrag

If there are no other processes writing to the drive with your sql datafiles
then the autogrow should extend itself onto the end of the datafile. If this
is where Oneworld is installed (and you know how badly oneworld fragments a
drive) and your autogrow is set to a small increment then you will probably
incur a performance hit, as the os tries to find contiguous disk space to
allocate. You should always try and give your datafiles some headroom to
grow so the above problems aren't encountered.
Regards
Kieran Fitzgerald

RE: RE: SQL Server 2000 and Defrag

This would also depend on other things: RAID configuration, NTFS
configuration, DB usage patterns, etc.

In general, it's not likely to happen: the files are usually spread across
multiple disks in a RAID anyway plus file chunk sizes are usually
sufficiently large and the DB Server is doing scattered random reads most of
the time, the situations where the file fragmentation would affect
performance are very few and even if this is to happen it would indicate
configuration drawbacks elsewhere.

The bottom line is: don't worry about it, or just use the
backup-reformat-restore approach occasionally if you feel it helps...