467,077 Members | 981 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,077 developers. It's quick & easy.

sql server 2000 Table Maintenance

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

Jul 23 '05 #1
  • viewed: 1356
Share:
1 Reply
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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by datapro01@yahoo.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.