I have an extremely large table that is starting to slow down
production. I want to keep it for historical queries, but want to have
it fast for daily production activity. The daily activity will never
access records older than a few days. One of the difficult things
about this table is there is a lot of foreign keys that reference the
table.

So, I'm thinking of doing this:

1) Keep a "live" archive of the table using INSERT, UPDATE, and DELETE
triggers.
2) Having a job that disables the DELETE triggers and will delete
records older than 1 week.
3) Of course, part of this is also deleting the records that reference
this table.
4) Doing the same thing, with other large tables including, of course,
the ones that reference this table.

It seems the trick, mostly, is to delete the records from various
tables in order, so that none of the foreign keys are left hanging.

Is this a reasonable approach? Is there a more straight forward/built-
in technique for doing this?

We examined partitioning and this does not seem to be a practical
approach because, amoung other things, some of the tables are self
referencing and these references would cross partition boundaries.

Thanks!

- Brian

On Mon, 3 Dec 2007 15:43:05 -0800 (PST), Brian - Support
<mail (AT) brianrice (DOT) com> wrote:

What do you consider "extremely large"?

A query like:
select * from SomeTable
where SomeDate between 'x' and 'y'
should be fast if SomeDate is indexed and x and y are only a few days
apart, even if there are millions of rows. That's the power of
indexing. So it should be fast to select the "active records" for use
in the front-end application.

-Tom.

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.

Also, we would put the larger "archive" tables in a seperate filegroup
so that we can do frequent backups of the smaller tables for quicker
emergency restores.

Brian - Support (mail (AT) brianrice (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx