sql server 2000 Table Maintenance

[rick]

sql server 2000

I am currently maintaining a table that contains 30 Million+ records,
30 columns, and 11 indexes and will double within the next six mouths.
The application that accesses this table, mainly for read only
purposes, runs without any problems. We have begun using Crystal
reports and are now having problems. When we create reports that
accesses the large table our server has significant performance dip.
The application begins to time out and the reports take a very long
time, even with simple selects on indexed field.

I have began looking into partitioning the large table on its key field
and creating a partition view. But from what I have read this will
only help if we key on the partitioned field. And all other searches
will actually take a little longer.
Archiving old data is not an option. All the data is being used
Any suggestions will be appreciated. Thanks in advance.

Rick

[Erland Sommarskog]

rick (ri**@united-directories.com) writes:

I am currently maintaining a table that contains 30 Million+ records,
30 columns, and 11 indexes and will double within the next six mouths.
The application that accesses this table, mainly for read only
purposes, runs without any problems. We have begun using Crystal
reports and are now having problems. When we create reports that
accesses the large table our server has significant performance dip.
The application begins to time out and the reports take a very long
time, even with simple selects on indexed field.

I have began looking into partitioning the large table on its key field
and creating a partition view. But from what I have read this will
only help if we key on the partitioned field. And all other searches
will actually take a little longer.
Archiving old data is not an option. All the data is being used
Any suggestions will be appreciated. Thanks in advance.

If the application is an OLTP type of thing, it might be that you need
to set up a report server which you feed through replication or log
shipping. Reports that queries the database in all sorts of way, can
easily cause locking problems for updates. This is particularly prone
to happen if users can define their own reports, as you then have
little control over the queries.

It might also be that you need to review the indexing on the table.
11 indexes may be a lot, but it may not be the right indexes.

Note also that just because a column is indexed, SQL Server may not
use that index, if it estimates using the index will be more expensive
than scanning the table.

One way to get a head start in such an analysis is to use the Index
Tuning Wiazard.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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