VERY LARGE TABLE - Performance - Design - Insert - Delete
Posted on 2004-08-27
We have a very large table in a database (100 million - 200 million rows or even more) in MS SQL Server 2000.
Each row contains a timestamp and we have a clustered index on the timestamp. (Please observe the clustered index on the time when answering the questions).
Our recovery model is set to simple as we do not care about the transaction log.
Many new records (about 1 million/day) is inserted into the table 24-7.
Question 1: When we delete old data this takes a very long time. What can we do to speed up the deletion of old data? Do the server have to move rows due to the clustered index (in my eyes this should not matter because we are always deleting from the beginning of the table and inserting at the end)? We can not do a TRUNCATE table because we do not want to delete all data, only the oldest!
Question 2: Does it matter what the table size are? Or can we grow one table in the database up to 1 TB without affecting performance? Or should we gain from splitting the one big table into several smaller (currently we only have two discs with RAID-1)? I understand that we would gain from this if we had more discs. But I wonder if it matters when we only have one disc?
Question 3: Is it important (do we need it at all) to rebuild the indexes if we always deleting from the beginning and inserts new data at the end? In my opinion this should not be necessary as we have the clustered index on the timestamp and therefore 8K pages and 64K extents will be deallocated at the beginning and new extents are allocated at the end?
Question 4: Do we gain performance by NOT inserting data at the same time as we are doing deletes? I think that this should not matter that much because again new data is inserted at the end of the table and old data is deleted from the beginning.
Question 5: I have at rare occasions seen that we got a lock resource error (native error 1204), when inserting and deleting at the same time. I think it is strange because the need for locks should not be that big when there are only 2 threads running at the same time in the database, one that inserts data at the end of the table and one that deletes it at the beginning of the table. Any comments on why this error occurs.
PLEASE help me with the above questions!!!
I am very grateful for all the help I can get!