rick (rick@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, esquel@sommarskog.se

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