SEO

#1 (**permalink**) 02-29-2008, 05:33 AM

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

#2 (**permalink**) 02-29-2008, 05:33 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

sql server 2000 Table Maintenance