Posted by Erland Sommarskog on 12/04/07 23:02

Brian - Support (mail@brianrice.com) writes:
> A handful of tables with 10 - 20 million records.
>
> For one thing, we're having to do update statitistics quite frequently
> or performance slows down, and the update statistics is taking 3-4
> minutes for each table (at 2% sampling) and using quite a bit of CPU
> when it runs.

10-20 millions rows is not that much. 3-4 minutes for 2% sample sound
a lot. Then again, what is the average row size?

One thing that we found when we want to put UPDATE STATISTICS in an
maintenance job, was that adding the WITH INDEX clause gave us good
performance. This means that we neglected the statistics on non-
indexed columns. Which may or may not be acceptable.

As for your archiving scheme, it's difficult to comment without knowing
all the details. But beware that the DELETE operations can be painful,
particularly, if there is activity in the other end of the table while
the DELETE is running.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]