Hi Jim,
If I may be permitted to give you an answer on this?
FYI, I've worked as a SQL Server Production DBA for 12+ years,so have
encountered this situation many, many times.

Database fragmentation happens at two levels. Period :

1. Within the database files. The mdfs, ldfs, ndfs or whatever you
name their extensions. Badly fragmented database files  WILL, 100%
guaranteed affect your database's performance.
The database reads and writes data to a file in the file system. If
that file is spread across the disk badly enough, you will get bad
performance as the disk head has to move around the disk more to read
the data, thrashing itself to death. It's that simple. This is disk
file fragmentation.

2. Within the database itself, at the page level. This means that the
index pages get out of order within the .mdf database file. The file
itself on disk may be one single, contiguous block, but if the index
pages are jumbled up and spread out within that file, it will also
affect database performance. This is index fragmentation.

If you have one or the other you *may* get a performance problem. If
you have both badly enough, you definitely will have one.

But first of all, establish if you really do need to do anything. If
your users are happy with their response times, and the database isn't
slowing to a halt, then there may not be any need to do anything,
though preventative maintenance is always the preferred option. If
things are creaking at the seams, Linchi's advice on baselining your
server is very wise. No point in regularly defragging for six hours
each night if you get no benefit !  Do some simple user measurements -
stopwatch the time from entering a zip code to customer data coming
back, ten times before and after the defrag. Throw away the fastest
and slowest and take an average for each and compare.

My advice would be:
First, don't defrag your disk files in the day time when users are
using the database, or during critical periods at night (eg: during
backup operations, batch jobs etc).
Lots of conflicting disk I/O between SQL Server and the disk defrag
job means timeouts, extended backup Windows and irate users.
Personally, I'd schedule it in for once a month max. If as Andrew
rightly said you've sized your database *files* so that they're not
always growing, they shouldn't get heavily fragmented at the disk
level too quickly.

Second, the built in Windows defragmenter is better than nothing, but
not great.
I've not used Raxco's, but heard it is a very good product. Likewise
the server edition of Diskeeper (note that the Windows defragger is a
cut down version of the Diskeeper product, but the latter has more
options).
Each will argue the virtues of these products - your choice.

Third, most third party vendors don't size their databases correctly,
either initially or for growth, so you typically have a scenario where
a busy db (eg a customer call centre db) is extending itself by 10Mb
every few hours/days. Not good. It is in this scenario where you will
find database fragmentation at the file level. But your vendor says
don't change the size, which *may* be ridiculous, depending on the
current database size and how regularly it is growing. The db will
have to either autogrow as data is added to the database and the
current maximum file size is reached, or the database will come to a
halt when the database file(s) are full and you won't have a usable
system. The former is the more likely scenario, but unless they've
chosen a large growth size, a busy database will fragment badly and
quickly, requiring a disk level defrag. If it's small and relatively
unused, no need to worry so much. If it grows frequently, then your
database app vendor needs educating. If not, they may have a valid
point, but it's worth addressing as some vendors are plain lazy or
lacking database skills.

Fourth, if you decide you need to defrag your hard drives, then always
defrag your SQL Server indexes AFTER the hard drive defrag. If you do
it the other way around, you risk re-distributing your nicely defrag'd
indexes randomly across the disk surface. I've seen database system
where this has been done in the wrong order and they get slower rather
than quicker. Always disk defrag then index defrag, if you're going to
do a disk defrag at all. With regard to defrag products, if you're not
versed in T-SQL commands or can't face the tedium of writing your own
scripts / debugging someone else's, you can plump for a tool such as
Visual Defrag or SQL Defrag Manager, which both ease the pain of
getting the job done and make things quicker in a large environment
with loads of servers and databases. But always use them AFTER you've
done the disk defrag, I can't stress that enough.

Fifth, with regard to defrag frequency, it depends on numerous
factors. With disk files, how large your database is sized, how
quickly it grows, the growth rate of your business's databases, and
the frequency of inserts, deletes and updates all play a part. There
is no simple answer to this, it all depends on the usage profile of
your particular database. But Andrew's advice to manually grow the
database so that it's good for the next 6-12 months is good, so long
as you can predict your growth rates. With regard to index defrags, if
your database is very dynamic, you'll be re-indexing every night.
Quieter databases, once a week/month or less. It all depends.

Finally, consider defragging your non database files one night and
your database files (mdfs, ldfs) the next, and repeating over several
nights, at least initially. In a badly fragmented system with large
drives holding a lot of data this will cut down your defrag time into
two chunks. This can be important if your overnight maintenance window
is small. Depending on the product and the amount of disk space, you
may have to do this 2-3 times before you file are optimally organised.
Alternatively, schedule the job to run over a public holiday starting
the previous evening, if this is possible. And make sure there's
15-20% free disk space before the disk defrag starts.

Whatever, if you're wise you'll always ensure that you've got backups
before you reorganise a database's index pages. Never lost one yet as
a result of a disk or an index defrag, but there's always a first
time....
Martin Williams.

Hi Jim,
If I may be permitted to give you an answer on this?
FYI, I've worked as a SQL Server Production DBA for 12+ years,so have
encountered this situation many, many times.

Database fragmentation happens at two levels :

1. Within the database files. The mdfs, ldfs, ndfs or whatever you
name their extensions. Badly fragmented database files will, 100%
guaranteed affect your database's performance.
The database reads and writes data to a file in the file system. If
that file is spread across the disk badly enough, you will get bad
performance as the disk head has to physically move across the surface
of the disk more to read the data, thrashing itself to death. This is
disk file fragmentation.

2. Within the database itself, at the data page level. Here, the index
pages get out of order within the .mdf database file. The file itself
on disk may be one single, contiguous block, but if the index pages
are jumbled up and spread out within that file, it will also affect
database performance. This is index fragmentation.

If you have one or the other you *may* get a performance problem. If
you have both badly enough, you definitely will have one.

But first of all, establish if you really do need to do anything. If
your users are happy with their response times, and the database isn't
slowing to a halt, then there may not be any need to do anything,
though preventative maintenance is always the preferred option. If
things are creaking at the seams, Linchi's advice on baselining your
server is very wise. No point in regularly defragging for six hours
each night if you get no benefit !  Do some simple user measurements
at the front end. Simply stopwatch the time from entering a zip code
to customer data coming back, ten times before and after the defrag.
Throw away the fastest and slowest and take an average for before and
after the initial defrag, and compare.

Other advice I'd recommend would be:

First, don't defrag your disk files in the day time when users are
using the database, or during critical periods at night (eg: during
backup operations, batch jobs etc).
Lots of conflicting disk I/O between SQL Server and the disk defrag
job means timeouts, extended backup Windows and irate users.
Personally, I'd schedule it in for once a month max. If as Andrew
rightly said you've sized your database *files* so that they're not
always growing, they shouldn't get heavily fragmented at the disk
level too quickly.

Second, the built in Windows defragmenter is better than nothing, but
not great.
I've not used Raxco's, but have heard it is a very good product.
Likewise the server edition of Diskeeper (note that the Windows
defragger is a cut down version of the Diskeeper product, but the
latter has more options).
Each will argue the virtues of these products - your choice.

Third, most third party vendors don't size their databases correctly,
either initially or for growth, so you typically have a scenario where
a busy db (eg a customer call centre db) is extending itself by 10Mb
every few hours/days. Not good. It is in this scenario where you will
find database fragmentation at the file level. But your vendor says
don't change the size, which *may* be ridiculous, depending on the
current database size and how regularly it is growing. The db will
have to either autogrow as data is added to the database and the
current maximum file size is reached, or the database will come to a
halt when the database file(s) are full and you won't have a usable
system. The former is the more likely scenario, but unless they've
chosen a large growth size, a busy database will fragment badly and
quickly, requiring a disk level defrag. If it's small and relatively
unused, no need to worry so much. If it grows frequently, then your
database app vendor needs educating. If not, they may have a valid
point, but it's worth addressing as some vendors are plain lazy or
lacking database skills.

Fourth, if you decide you need to defrag your hard drives, then always
defrag your SQL Server indexes AFTER the hard drive defrag. If you do
it the other way around, you risk re-distributing your nicely defrag'd
indexes randomly across the disk surface. I've seen database system
where this has been done in the wrong order and they get slower rather
than quicker. Always disk defrag then index defrag, if you're going to
do a disk defrag at all. With regard to defrag products, if you're not
versed in T-SQL commands or can't face the tedium of writing your own
scripts / debugging someone else's, you can plump for a tool such as
Visual Defrag or SQL Defrag Manager, which both ease the pain of
getting the job done and make things quicker in a large environment
with loads of servers and databases. But always use them AFTER you've
done the disk defrag, I can't stress that enough.

Fifth, with regard to defrag frequency, it depends on numerous
factors. With disk files, how large your database is sized, how
quickly it grows, the growth rate of your business's databases, and
the frequency of inserts, deletes and updates all play a part. There
is no simple answer to this, it all depends on the usage profile of
your particular database. But Andrew's advice to manually grow the
database so that it's good for the next 6-12 months is good, so long
as you can predict your growth rates. With regard to index defrags, if
your database is very dynamic, you'll be re-indexing every night.
Quieter databases, once a week/month or less. It all depends.

Finally, consider defragging your non database files one night and
your database files (mdfs, ldfs) the next, and repeating over several
nights, at least initially. In a badly fragmented system with large
drives holding a lot of data this will cut down your defrag time into
two chunks. This can be important if your overnight maintenance window
is small. Depending on the product and the amount of disk space, you
may have to do this 2-3 times before you file are optimally organised.
Alternatively, schedule the job to run over a public holiday starting
the previous evening, if this is possible. And make sure there's
15-20% free disk space before the disk defrag starts.

Whatever, if you're wise you'll always ensure that you've got backups
before you reorganise a database's index pages. Never lost one yet as
a result of a disk or an index defrag, but there's always a first
time....

Dave Ellis.