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
没有评论:
发表评论