Disk Fragmentation on W2K Server/SQL Server 2000

Hi, I'm hoping one of you SQL gurus out there will help me with a debate bet
ween myself, a simple Network Admin. and my colleague, an SQL DBA. The W2K/
SQL 2000 box is having performance problems so I was asked to check on it.
Now, in most environments I
have worked in, I took care of the physical server and OS and the DBA/Develo
per types did their own thing. In my current work environment, everyone has
been accustomed to taking care of their own boxes, the DBA included, so the
re are some issues with me
doing my job at all. Anyway, I did a performance analysis of the OS/Hardwar
e and configuration. I also researched the Best Practices for SQL 2000 Serve
r and after a week of collecting metrics, gave the information to the DBA.
There did not appear to be
any disk, memory, paging etc. problems but the one thing that really stood o
ut to me was that the disks were 49% fragmented and the files on those disks
93 and 99% fragmented. Primarily database and log files. Again, I just pr
ovided the info as I do no
t want to step on the DBAs toes. The DBA however, reacted defensively and s
aid that she has *never* defragged an SQL server and in searching through MS
DN, found *no* evidence that there is ever a need to defrag one. Further, t
hat if SQL server could now
find the files in their fragmented state, why was there a problem? If the d
rives were defragged, how would SQL server be able to find the files? She w
as serious. Now, I'm being frowned on as not knowing what I'm talking about.
I'm the newest member of
the team, but not a newbie in the field. I have worked with Microsoft produc
ts now for many years and I know that every Windows box PC or Server needs t
o be defragged and occasionally rebooted as a part of routine maintenace. T
here needs to be a mainten
ance windows for doing so. Having a 99.9% uptime record will not matter when
those drives grind to a halt. What I keep searching for is some reference
from Microsoft or other known expert or publication that will state this as
it pertains to SQL server.
Most of the SQL manuals leave out any OS administration information. Anyone
have any suggestions or comments? Microsoft, are you out there? Please He
lp! TIAmy two cents:
Databases if set up as auto grow and auto shrink, will definitely fragment
disk drives over time. Defragmentation the disks will help improve
performance.
Now, inside a database, there is a page split problem that causes
fragmentation too, It's the DBA's job to reindex tables on a regular basis
to fix index fragmentation. I think you should first get an idea of how the
DBA maintains the index defragmentation, then work on the physical disk
defragmentation. Also there are a lot of other factors affecting database
performance.
Richard
"cdub" <cdub@.newsguy.com> wrote in message
news:29C67933-B2CB-43F3-A498-E8EABD2DB936@.microsoft.com...
> Hi, I'm hoping one of you SQL gurus out there will help me with a debate
between myself, a simple Network Admin. and my colleague, an SQL DBA. The
W2K/SQL 2000 box is having performance problems so I was asked to check on
it. Now, in most environments I have worked in, I took care of the physical
server and OS and the DBA/Developer types did their own thing. In my
current work environment, everyone has been accustomed to taking care of
their own boxes, the DBA included, so there are some issues with me doing my
job at all. Anyway, I did a performance analysis of the OS/Hardware and
configuration. I also researched the Best Practices for SQL 2000 Server and
after a week of collecting metrics, gave the information to the DBA. There
did not appear to be any disk, memory, paging etc. problems but the one
thing that really stood out to me was that the disks were 49% fragmented and
the files on those disks 93 and 99% fragmented. Primarily database and log
files. Again, I just provided the info as I do not want to step on the DBAs
toes. The DBA however, reacted defensively and said that she has *never*
defragged an SQL server and in searching through MSDN, found *no* evidence
that there is ever a need to defrag one. Further, that if SQL server could
now find the files in their fragmented state, why was there a problem? If
the drives were defragged, how would SQL server be able to find the files?
She was serious. Now, I'm being frowned on as not knowing what I'm talking
about. I'm the newest member of the team, but not a newbie in the field. I
have worked with Microsoft products now for many years and I know that every
Windows box PC or Server needs to be defragged and occasionally rebooted as
a part of routine maintenace. There needs to be a maintenance windows for
doing so. Having a 99.9% uptime record will not matter when those drives
grind to a halt. What I keep searching for is some reference from Microsoft
or other known expert or publication that will state this as it pertains to
SQL server. Most of the SQL manuals leave out any OS administration
information. Anyone have any suggestions or comments? Microsoft, are you
out there? Please Help! TIA|||I agree with Richard. I've seen SQL performance affected by fragmentation
and auto-grow. The best way to handle it is usually to plan the size of the
database you need, defrag first, and then allocate the space that you need.
Christian Smith
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:uEp2zCo8DHA.2656@.TK2MSFTNGP11.phx.gbl...
> my two cents:
> Databases if set up as auto grow and auto shrink, will definitely fragment
> disk drives over time. Defragmentation the disks will help improve
> performance.
> Now, inside a database, there is a page split problem that causes
> fragmentation too, It's the DBA's job to reindex tables on a regular basis
> to fix index fragmentation. I think you should first get an idea of how
the
> DBA maintains the index defragmentation, then work on the physical disk
> defragmentation. Also there are a lot of other factors affecting database
> performance.
>
> Richard
> "cdub" <cdub@.newsguy.com> wrote in message
> news:29C67933-B2CB-43F3-A498-E8EABD2DB936@.microsoft.com...
> between myself, a simple Network Admin. and my colleague, an SQL DBA. The
> W2K/SQL 2000 box is having performance problems so I was asked to check on
> it. Now, in most environments I have worked in, I took care of the
physical
> server and OS and the DBA/Developer types did their own thing. In my
> current work environment, everyone has been accustomed to taking care of
> their own boxes, the DBA included, so there are some issues with me doing
my
> job at all. Anyway, I did a performance analysis of the OS/Hardware and
> configuration. I also researched the Best Practices for SQL 2000 Server
and
> after a week of collecting metrics, gave the information to the DBA.
There
> did not appear to be any disk, memory, paging etc. problems but the one
> thing that really stood out to me was that the disks were 49% fragmented
and
> the files on those disks 93 and 99% fragmented. Primarily database and
log
> files. Again, I just provided the info as I do not want to step on the
DBAs
> toes. The DBA however, reacted defensively and said that she has *never*
> defragged an SQL server and in searching through MSDN, found *no* evidence
> that there is ever a need to defrag one. Further, that if SQL server
could
> now find the files in their fragmented state, why was there a problem? If
> the drives were defragged, how would SQL server be able to find the files?
> She was serious. Now, I'm being frowned on as not knowing what I'm talking
> about. I'm the newest member of the team, but not a newbie in the field.
I
> have worked with Microsoft products now for many years and I know that
every
> Windows box PC or Server needs to be defragged and occasionally rebooted
as
> a part of routine maintenace. There needs to be a maintenance windows for
> doing so. Having a 99.9% uptime record will not matter when those drives
> grind to a halt. What I keep searching for is some reference from
Microsoft
> or other known expert or publication that will state this as it pertains
to
> SQL server. Most of the SQL manuals leave out any OS administration
> information. Anyone have any suggestions or comments? Microsoft, are you
> out there? Please Help! TIA
>sql

Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!
Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>
|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
--
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

Disk fragmentation after backup

I got heavily disk fragmented after a full database backup. The disks
configured in RAID 10 and used to store just 1 full backup file daily. I
used the option "with init" on the backup statement. If you have experience
this or know the solution to this issue, please help. Thanks!Relax. Is it really affecting performance? How do you know?
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>|||Try deleting the file once just before you do the full backup. If the disk
does not have other files on it then the new file should be contiguous.
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:6A5A83B1-1BE6-418E-956F-1E25608E9B62@.microsoft.com...
>I got heavily disk fragmented after a full database backup. The disks
> configured in RAID 10 and used to store just 1 full backup file daily. I
> used the option "with init" on the backup statement. If you have
> experience
> this or know the solution to this issue, please help. Thanks!
>

disk fragmentation

I have a Dell PowerEdge 2850 with Windows 2003 server. 2 disk drives with RAID. My databases are fragmented on the drive and I need to defrag the drive at least once a week. My database files are fragmented. Defragger cleans up the files but as soon as I start running processes again, the files fragment and system response time suffers.

What can I do to keep the files from fragmenting?

calculate the database growth

and set the file increment in the db option

bigger than that. i meann just a little bigger

|||

Question : does file fragmentation really impact the performance of a DB ?

I mean : by construction, the data in files are fragmented, and access to data in DB always require a lot of disk seek. Sequential access through files should be marginal. So, fragmentation of the whole file should not imply a big performance impact.

Am I correct ?

|||

How are you measuring the fragmentation?

There are two types of fragmentation here:

1) The normal filesystem fragmentation of the database files. This should have minimal to nil impact unless you do a lot of table-scans. On the other hand, you shouldn't be getting this sort of fragmentation unless you are constantly growing your database. Far better to size it for growth initially than to allow it to grow incrementally.

2) Internal fragmentation of the information within the database files (as measured by commands like DBCC SHOWCONTIG). THis has nothing to do with the physical files being fragmented on disk, and is resolved by using SQL techniques such as DBCC INDEXDEFRAG or ALTER INDEX REBUILD.

Disk Fragmentation

What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?

If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?

Clivewould running a trace after defragmentation, and running it again when the disk gets fragmented aid in this? you could use the built in defragger, but i would run it when the server is at it's quietest.