|
|
Posted by Erland Sommarskog on 11/24/05 00:36
Zarrin (zlangari@yahoo.com) writes:
> My purge is a daily job, and my question is how this work on a heavy
> load operational database? I mean thousand of records are written into
> my tables (the same table that I want to purge some rows from) every
> second. While I am doing the copy to temp table and drop the table what
> happens to those operational data?
I don't think it is a good idea to create new tables every day. Particularly
not if data are being added as you delete.
I can think of two ways:
1) Keep it all in the same table. It's imperative that the condition for
the DELETE is aligned with the clustered index, so that INSERTs are
not blocked by the DELETE:
2) Use a partitioned view. A partitioned view consists of a number
tables with the same structure and where the PK has a CHCEK constraint
the defines what does into which table. The table are then combined
in a SELECT with UNION ALL. Again, the partition condition has to be
aligned for the condition for the DELETE. More exactly so that a
DELETE is simple a TRUNCATE TABLE on a single table. Since you do this
on daily basis, it will be a lot of tables, unless there is a round-
robin mechanism. You can of course change the view defintion every
now and then, and while this is a swift operation, you must still lock
out the INSERT process, so it does not try to insert into a non-
existing view.
Read more about partitioned views in Books Online.
--
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
Navigation:
[Reply to this message]
|