fragmented database files

I have a sql server 2005 server, running on windows server 2003 sp2 on
a 32 bit dual processor dell machine.
I have have some smallish databases on this machine. The drives which
hold the MDF and LDF files have become badly fragmented. Some of the
log files have been split into tens of fragments. The distribution
database has over 100 fragments. I can see that this is bad for
performance, how bad? In the past, this machine has become very
unhappy when producing replication snapshots (with lots of page io
latch* waits). Now it seems to have become CPU bound (showing SOS
scheduler waits). I'm not saying these things are connected, but just
throwing out some data.
Any thoughts on defragmenting, reducing fragmentation in the future
and any connection to (recently) worsening performance?
One idea I had is that there is plenty of space on the drives (>50%)
if I shut down sql server and then moved the files to another physical
drive and then copied them back, would that put them back in a non-
fragmented way (given that there is a contiguos block of free space
that is big enough.. it looks like there is from defrag console, but
I'm not sure how accurate that is)
thanks for any help
Hi Sam
Disc fragmentation is usually less of an issue if you have a dedicated file
server and don't (auto) shrink the databases. On a database server (such as
a development PC) which has shared usage it may be more of an issue
especially if you are continually backing up/restoring databases (or if you
shrink the database). See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp for more.
To defragment the database files you will either have to detach the database
or stop SQL server while you run a disc defragmenter. Tools such as
diskeeper can help reduce fragmentation.
John
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>
|||If your database files are as extremely fragmented as you describe
then I suggest a few things.
1) This sort of problem can result from using autoshrink. Check to
see if it is being used, and if it is remove it! At the same time
size the files so that autogrow is not needed.
2) Defragment the drives. The database file fragmentation might can't
be helping your performance problems, and might improve things.
3) Monitor the system. Expand the files before they autogrow - the
worst time to expand a file is when a transaction has to wait for the
growth to complete. Add space in large chunks, not small bits.
Roy Harvey
Beacon Falls, CT
On Fri, 28 Dec 2007 06:15:47 -0800 (PST), "sam.m.gardiner"
<sam.m.gardiner@.gmail.com> wrote:

>I have a sql server 2005 server, running on windows server 2003 sp2 on
>a 32 bit dual processor dell machine.
>I have have some smallish databases on this machine. The drives which
>hold the MDF and LDF files have become badly fragmented. Some of the
>log files have been split into tens of fragments. The distribution
>database has over 100 fragments. I can see that this is bad for
>performance, how bad? In the past, this machine has become very
>unhappy when producing replication snapshots (with lots of page io
>latch* waits). Now it seems to have become CPU bound (showing SOS
>scheduler waits). I'm not saying these things are connected, but just
>throwing out some data.
>Any thoughts on defragmenting, reducing fragmentation in the future
>and any connection to (recently) worsening performance?
>One idea I had is that there is plenty of space on the drives (>50%)
>if I shut down sql server and then moved the files to another physical
>drive and then copied them back, would that put them back in a non-
>fragmented way (given that there is a contiguos block of free space
>that is big enough.. it looks like there is from defrag console, but
>I'm not sure how accurate that is)
>thanks for any help
>
|||1) You should set the database and log file sizes such that you never
actually HIT an autogrow. That mechanism should only be for exceptional,
unexpected situations. Making the files big enough to handle say 1.5 years
of data growth leads to a contiguous disk file (and thus sequential I/O) and
no fragmentation.
2) I had a client where I got back 18% throughput simeply by having them
defrag their disks. They had well over 300K database and log file fragments
though!! That is what can happen when you use default settings.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sam.m.gardiner" <sam.m.gardiner@.gmail.com> wrote in message
news:665d5dc6-832b-41af-a8a9-ca141dc7e3b7@.c4g2000hsg.googlegroups.com...
>I have a sql server 2005 server, running on windows server 2003 sp2 on
> a 32 bit dual processor dell machine.
> I have have some smallish databases on this machine. The drives which
> hold the MDF and LDF files have become badly fragmented. Some of the
> log files have been split into tens of fragments. The distribution
> database has over 100 fragments. I can see that this is bad for
> performance, how bad? In the past, this machine has become very
> unhappy when producing replication snapshots (with lots of page io
> latch* waits). Now it seems to have become CPU bound (showing SOS
> scheduler waits). I'm not saying these things are connected, but just
> throwing out some data.
> Any thoughts on defragmenting, reducing fragmentation in the future
> and any connection to (recently) worsening performance?
> One idea I had is that there is plenty of space on the drives (>50%)
> if I shut down sql server and then moved the files to another physical
> drive and then copied them back, would that put them back in a non-
> fragmented way (given that there is a contiguos block of free space
> that is big enough.. it looks like there is from defrag console, but
> I'm not sure how accurate that is)
> thanks for any help
>