Poor performance

[David Gray]

Hello Gurus,

SQL Server 2000
Windows 2003 Server, Standard Edition.

Firstly I'm not a SQL server DBA but have a little experience with
Oracle 9i and Oracle Rdb.

An application that I've inherited has started performing very very
slowly over the last few days, as far as I know there have been no
modifications or changes in the volume of data the db is holding. The
database is in simple logging mode and it's updated twice per day from
and Oracle Rdb database.

The SQL database is all in the primary filegroup and is sat on four
disks (RAID mirrored) which form one logical disk of 128gb. It
consists of approx 120 tables, each with a primary key and a number of
unique indicies per table.

Apart from a twice a day update in the small hours the only access to
this database is read only. Users are connecting via the web and
using predefined Crystal reports to retrieve data.

Questions then:

Should this db have more than one filegroup and should I put the
indicies in a different group? Is this relevant when the underlying
storage is RAID?

Should it be using mirrored RAID or should it be striped or should I
steer clear of RAID for a database?

The indicies have not been rebuilt for some time (probably one year).
Is this something I should be doing once per day/week as I do have the
downtime window?

Should I be doing a database/table reorg at fairly regular intervals?

I'm going to generate a workload file from SQL profiler and see what,
in conjunction with the Index tuning wizard it suggests.

Appreciate that poor performance & tuning is a huge subject and not an
exact science but any tips or comments would be gratefully received.

Regards
Dave.

[Erland Sommarskog]

David Gray (po****@spamcop.net) writes:

An application that I've inherited has started performing very very
slowly over the last few days, as far as I know there have been no
modifications or changes in the volume of data the db is holding. The
database is in simple logging mode and it's updated twice per day from
and Oracle Rdb database.
But as data is added to he database, statistics changes, and the
optimizer may come to different conclusions about query plans. Sometimes
to the worst.
Should this db have more than one filegroup and should I put the
indicies in a different group? Is this relevant when the underlying
storage is RAID?

Should it be using mirrored RAID or should it be striped or should I
steer clear of RAID for a database?
Since it appears that the database performance has degraded so all at
once, I doubt that hardware configuration is the right medicine.
The indicies have not been rebuilt for some time (probably one year).
Is this something I should be doing once per day/week as I do have the
downtime window?
Absolutely!
Should I be doing a database/table reorg at fairly regular intervals?
That is actually the same thing as rebuilding indexes, at least as long
as you rebuild the clustered indexes.

Note that if you have tables without clustered indexes, they cannot
be rebuild by other means than creating a clustered index and then
dropping it. (Thus it's a good idea to have a clustered index on every
table.)
I'm going to generate a workload file from SQL profiler and see what,
in conjunction with the Index tuning wizard it suggests.

That may be a good idea anyway, because about every database has its
own set of poorly indexed queries. But I would expect a DBCC DBREINDEX
on all tables to have considerable effect.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gang He [MSFT]]

Your indexes probably got a lot of fragmentations due to the twice daily
updates over time. You can use DBCC SHOWCONTIG to find out the degree of
fragmentation. I'd strongly suggest you to rebuild all the indexes if they
haven't been rebuilt for one year. Afterwards you should consider to
schedule periodical index reorg/defrag tasks to keep the fragmentation at a
low level.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"David Gray" <po****@spamcop.net> wrote in message
news:1r********************************@4ax.com...

Hello Gurus,

SQL Server 2000
Windows 2003 Server, Standard Edition.

Firstly I'm not a SQL server DBA but have a little experience with
Oracle 9i and Oracle Rdb.

An application that I've inherited has started performing very very
slowly over the last few days, as far as I know there have been no
modifications or changes in the volume of data the db is holding. The
database is in simple logging mode and it's updated twice per day from
and Oracle Rdb database.

The SQL database is all in the primary filegroup and is sat on four
disks (RAID mirrored) which form one logical disk of 128gb. It
consists of approx 120 tables, each with a primary key and a number of
unique indicies per table.

Apart from a twice a day update in the small hours the only access to
this database is read only. Users are connecting via the web and
using predefined Crystal reports to retrieve data.

Questions then:

Should this db have more than one filegroup and should I put the
indicies in a different group? Is this relevant when the underlying
storage is RAID?

Should it be using mirrored RAID or should it be striped or should I
steer clear of RAID for a database?

The indicies have not been rebuilt for some time (probably one year).
Is this something I should be doing once per day/week as I do have the
downtime window?

Should I be doing a database/table reorg at fairly regular intervals?

I'm going to generate a workload file from SQL profiler and see what,
in conjunction with the Index tuning wizard it suggests.

Appreciate that poor performance & tuning is a huge subject and not an
exact science but any tips or comments would be gratefully received.

Regards
Dave.