Fragmented array

Does anyone have any advice about fragmentation/defragmentation of RAID arrays. Basically W2K Defrag reports 80% fragmentation on a partition and our Net Support are unsure whether to defrag it because it is a h/w RAID solution. As its a SQL Server it falls to me to decide
CheersJonatha
What is fragmented? Is it your mdf and ldf files, non-database files or everything
If it is your database files, I would guess you have autogrow on and are growing a very small percentage at a time. It is much better to grow your database in fewer large files, than lots of little files. Depending on disk space, when your database needs to be expanded grow it as much as you can, try for at least 6 months growth, more if possible
If that is the case your best bet is to create a new database with large contiguous data files. You would then need to copy all your database to it, using DTS, BCP or similar. You can not use backup and restore as this would re-create the database in the same style you have. You could try to create new filegroups and move your data into them and empty the old filegroups and delete them, this can be a bit messy
Good Luc
John|||Hi,
It is always good to defragment the array(s) every
fortnight. In your case It is very much needed as the
report shows 80% fragmentation. Use a utility like
DiskKeeper. There is a DiskKeeperLite version available
which u can try out. Defragmenting the arrays wont affect
SQL Server performance unless u r defragmenting the disks
in peak hours.
Also Look at the below article if you are concerned about
defragmenting SQL Server Database. The title is a bit
confusing as the user might think that one can defragment
the SQL Database directly using DiskKeeper, but, the
author explains initially that you need to perform certain
things to defragment the database "Internally", then about
DiskKeeper.
Keeping Your SQL Server Databases Defragmented with
Diskeeper
http://www.sql-server-performance.com/sql_fragmentation.asp
HTH
Regards
Thirumal Reddy M
Sys Admin
www.sstil.com
>--Original Message--
>Does anyone have any advice about
fragmentation/defragmentation of RAID arrays. Basically
W2K Defrag reports 80% fragmentation on a partition and
our Net Support are unsure whether to defrag it because it
is a h/w RAID solution. As its a SQL Server it falls to me
to decide!
>Cheers
>.
>|||comments embedded
"Thirumal" <treddym@.hotmail.NOSPAM.com> wrote in message
news:550a01c4004c$adc09350$a401280a@.phx.gbl...
> Hi,
> It is always good to defragment the array(s) every
> fortnight.
This kind of raised alarm bells for me. It sounds like a recommendation
based on folklore rather than evidence.
>In your case It is very much needed as the
> report shows 80% fragmentation.
It all depends on what you mean by 'needed'. I didn't see any evidence that
the original poster actually had a performance problem, still less that it
was caused by large times waiting on io and none at all that the io wait was
caused by file fragmentation.
> Use a utility like
> DiskKeeper. There is a DiskKeeperLite version available
> which u can try out. Defragmenting the arrays wont affect
> SQL Server performance unless u r defragmenting the disks
> in peak hours.
I'd at least start with the inbuilt tool which is free and does an ok job.
> Keeping Your SQL Server Databases Defragmented with
> Diskeeper
> http://www.sql-server-performance.com/sql_fragmentation.asp
It isn't a bad article as it does explain the problem with fragmentation in
general. I'd like it to go further and suggest how you might decide on when
to defragment.
I'd suggest the following to the original poster.
1. Don't use autoextend for data and log files, or if you do make sure the
increments are large. Try and size the datafiles up front and then monitor
free space and extend or add datafiles manually.
2. don't worry about defragmentation unless *all* of the following are true
- you have a defined response time problem (or a trend that predicts
that you will have one soon)
- you can identify waiting for io as the cause of the problem
- you cannot reduce the io requirements of the task.
- you can identify that you are waiting because of the delay in
skipping around the disk.
- you have at least a good indication that the impact of the
defragmentation process is not worse than the current system performance
levels.
all of the above conditions are satisified very rarely indeed and so i'd
suggest defragmentation of sqlservers filesystems should be equally rare.
3. don't buy a tool - use the inbuilt one.
--
Niall Litchfield
Oracle DBA
Audit Commission UK|||Hi Naill,
Good Suggestions!
Regards
Thirumal
>--Original Message--
>comments embedded
>"Thirumal" <treddym@.hotmail.NOSPAM.com> wrote in message
>news:550a01c4004c$adc09350$a401280a@.phx.gbl...
>> Hi,
>> It is always good to defragment the array(s) every
>> fortnight.
>This kind of raised alarm bells for me. It sounds like a
recommendation
>based on folklore rather than evidence.
>>In your case It is very much needed as the
>> report shows 80% fragmentation.
>It all depends on what you mean by 'needed'. I didn't see
any evidence that
>the original poster actually had a performance problem,
still less that it
>was caused by large times waiting on io and none at all
that the io wait was
>caused by file fragmentation.
>> Use a utility like
>> DiskKeeper. There is a DiskKeeperLite version available
>> which u can try out. Defragmenting the arrays wont
affect
>> SQL Server performance unless u r defragmenting the
disks
>> in peak hours.
>I'd at least start with the inbuilt tool which is free
and does an ok job.
>> Keeping Your SQL Server Databases Defragmented with
>> Diskeeper
>> http://www.sql-server-
performance.com/sql_fragmentation.asp
>It isn't a bad article as it does explain the problem
with fragmentation in
>general. I'd like it to go further and suggest how you
might decide on when
>to defragment.
>I'd suggest the following to the original poster.
>1. Don't use autoextend for data and log files, or if you
do make sure the
>increments are large. Try and size the datafiles up front
and then monitor
>free space and extend or add datafiles manually.
>2. don't worry about defragmentation unless *all* of the
following are true
> - you have a defined response time problem (or a
trend that predicts
>that you will have one soon)
> - you can identify waiting for io as the cause of
the problem
> - you cannot reduce the io requirements of the
task.
> - you can identify that you are waiting because
of the delay in
>skipping around the disk.
> - you have at least a good indication that the
impact of the
>defragmentation process is not worse than the current
system performance
>levels.
>all of the above conditions are satisified very rarely
indeed and so i'd
>suggest defragmentation of sqlservers filesystems should
be equally rare.
>3. don't buy a tool - use the inbuilt one.
>--
>Niall Litchfield
>Oracle DBA
>Audit Commission UK
>
>.
>