We have a table that stores audit records (Log in, log out that kind of thing), we already have a nightly job that archives any data that is older than 3 months.
However every week or two we get spikes in traffic and the table grows rapidly (compared to the tables normal growth). We have also noticed if we get too many rows in the table the performance goes off a cliff.
So what I'm looking for is an optimal way to keep a maximum of 3 million rows in the table as part of either as part of the nightly job above or as a separate nightly job. This job may be deleting upwards of half a million records a night, so performance is what we are looking for as the table is always being written to.
The table has a primary key of a long identity, and we are running SQL Server 2012.
We have another work thread to fix our performance issues with this table, this is just a stop gap until that work has been completed.
Best Answer
Thinking outside the box a little bit, but instead of a nightly job, what about a Table Trigger to handle it sooner so there's a much smaller batch to delete?
Basically you can have an after insert Trigger that checks the new size of the table, and deletes the amount of rows > 3 million. I'd imagine this would always be 1 row since the Trigger would fire on every
INSERT. Deleting 1 row should be very quick, but depending on the existing busyness of the table, I'm not sure if the additional contention for deleting will help or hurt more than a larger batch job at night. You'd have to test both ways and compare.Another trick you could use is create an Indexed View on top of the table that only brings back the latest 3 million records, and do your querying off the View instead of the Table. Then the Table you can prune and archive less frequently, in a routine maintenance window perhaps.
As far as determining the latest 3 million rows, you can store the max value of your identity column of your Table in another Table that only has 1 row to keep track of this. Then in your View you'd
INNER JOINto that TableON AuditTable.IdentityColumn >= MaxAuditIdentityValueTable.Value - 3000000.You can accomplish storing that max identity value a number of ways, one being an after insert Table Trigger as mentioned above. In this case, it should be an even more efficient Trigger because you're not locking your audit table when it fires (since you're not deleting from it), rather you're locking the
MaxAuditIdentityValueTableinstead, and since there's only 1 record toUPDATEin that table, it should be extremely fast. (By the way, please pick a better name than my example ofMaxAuditIdentityValueTable, I suck at naming things. :)