Defragging SQL Server Data Disk?

I've noticed disk performance on our SQL 2000 cluster
slowing down. The 100 GB disk with the database files is
on an array. Using the included 2000 defrag tool I did an
analyze and found the disk is highly fragmented. It's
been running for about 2 years and never been defraged.
Is it necessary or even safe to defrag the disk with the
databases? I would assume you would need to stop the SQL
service to do it. Would it even do any good? I'd almost
think that since there is a small number of files that
after turning it back up it wouldn't take long before
everthing is fragmented again, but maybe not to the extent
that it is now.Sam,
Stop the SQL Server service and defrag the disk. The primary cause of
file fragmentation is growing and shrinking of the SQL Server data
files. If using autogrow, then grow in large chunks, so that this
operation only occurs every few months or so, or even better longer
periods than that. Turn off autoshrink.
After you have defragged the data files, perform a DBCC DBREINDEX to
locically defrag your indexes. If you have a clustered index on all your
tables, this process will be more thorough.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||I agree 100% with Mark. Just want to add that you should make sure you have
valid backups before attempting the defrag. And I also want to emphasize to
stop the shrinking and growing.
--
Andrew J. Kelly SQL MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23Tr%23AB%23cEHA.2752@.TK2MSFTNGP12.phx.gbl...
> Sam,
> Stop the SQL Server service and defrag the disk. The primary cause of
> file fragmentation is growing and shrinking of the SQL Server data
> files. If using autogrow, then grow in large chunks, so that this
> operation only occurs every few months or so, or even better longer
> periods than that. Turn off autoshrink.
> After you have defragged the data files, perform a DBCC DBREINDEX to
> locically defrag your indexes. If you have a clustered index on all your
> tables, this process will be more thorough.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Sam Winston wrote:
> > I've noticed disk performance on our SQL 2000 cluster
> > slowing down. The 100 GB disk with the database files is
> > on an array. Using the included 2000 defrag tool I did an
> > analyze and found the disk is highly fragmented. It's
> > been running for about 2 years and never been defraged.
> > Is it necessary or even safe to defrag the disk with the
> > databases? I would assume you would need to stop the SQL
> > service to do it. Would it even do any good? I'd almost
> > think that since there is a small number of files that
> > after turning it back up it wouldn't take long before
> > everthing is fragmented again, but maybe not to the extent
> > that it is now.|||Hi
As an aside.
The fact that Defragger can even look at your array volumes means that your
disk is formatted with block sizes of 4kb or smaller.
Have a serious look at formatting your drives with larger block sizes, and
if possible, the same size as your RAID block sizes.
SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
improve your performance (8 or 64kb)
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sam Winston" <anonymous@.discussions.microsoft.com> wrote in message
news:4f3601c473dc$d7b2b8b0$a501280a@.phx.gbl...
> I've noticed disk performance on our SQL 2000 cluster
> slowing down. The 100 GB disk with the database files is
> on an array. Using the included 2000 defrag tool I did an
> analyze and found the disk is highly fragmented. It's
> been running for about 2 years and never been defraged.
> Is it necessary or even safe to defrag the disk with the
> databases? I would assume you would need to stop the SQL
> service to do it. Would it even do any good? I'd almost
> think that since there is a small number of files that
> after turning it back up it wouldn't take long before
> everthing is fragmented again, but maybe not to the extent
> that it is now.|||Mike,
I agree, 64k seems to be better for "standard" disks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Mike Epprecht (SQL MVP) wrote:
> Hi
> As an aside.
> The fact that Defragger can even look at your array volumes means that your
> disk is formatted with block sizes of 4kb or smaller.
> Have a serious look at formatting your drives with larger block sizes, and
> if possible, the same size as your RAID block sizes.
> SQL does I/O in 8 extents of 8kb each (64Kb). Having a larger block size may
> improve your performance (8 or 64kb)
> Regards|||I have been shrinking and growing alot. It hit me that's
what's resulting in the fragmentation. I'll add in some
overhead, try to stick with fixed sizes, and set the
autogrow to a large chunk.
We are adding a second array. 400 GB total that I'm
formatting as 2 200 GB disks. The server has six majort
databases all are over 5 GB and 3 are over 15 GB. The
current disks are 4k clusters. Larger clusters make
sense. For DBs of this size would'nt it make sense to go
ahead and do 64k?|||Sam,
Yep, 64k clusters would be good.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Sam Winston wrote:
> I have been shrinking and growing alot. It hit me that's
> what's resulting in the fragmentation. I'll add in some
> overhead, try to stick with fixed sizes, and set the
> autogrow to a large chunk.
> We are adding a second array. 400 GB total that I'm
> formatting as 2 200 GB disks. The server has six majort
> databases all are over 5 GB and 3 are over 15 GB. The
> current disks are 4k clusters. Larger clusters make
> sense. For DBs of this size would'nt it make sense to go
> ahead and do 64k?