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.


1 Answer 1


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 JOIN to that Table ON 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 MaxAuditIdentityValueTable instead, and since there's only 1 record to UPDATE in that table, it should be extremely fast. (By the way, please pick a better name than my example of MaxAuditIdentityValueTable, I suck at naming things. :)

  • 1
    On the topic of the performance hit of running a trigger on each INSERT, a job could just be scheduled to run every ten minutes or so. I know selecting the count shouldn't be enough to notice, but if they are getting insane spikes in access then those quick queries could add up fast. Mar 11, 2021 at 12:28
  • 1
    @JohnHerbert Yes, it really depends on the busyness of their Table and their setup for how performant the Trigger would be. But that's a good idea too. And they wouldn't even need to do a SELECT COUNT(*) to get the count, it's even more performant to use the system stored procedure sp_SpaceUsed (which is non-locking and I've always seen return instantaneously) to get the total number of rows in a table.
    – J.D.
    Mar 11, 2021 at 12:32
  • How long would it take to check if there are more than 3 million rows? To select those that need to be removed? Mar 12, 2021 at 7:29
  • @GerardH.Pile I'm guessing you're asking in regards to my first solution?...to your second question, that really depends on the busyness of his table (from a locking standpoint). Since the Trigger fires after every INSERT it should only be deleting 1 row for each row that was just inserted, if the table is greater than 3 million rows now, so it should be a relatively small batch and DELETE quite fast, especially with an integer identity he can use to determine which rows to DELETE. This is true as well for John Herbert's idea with a more frequent routine cleanup Job.
    – J.D.
    Mar 12, 2021 at 12:35
  • To your first question, getting the count of how many rows there are in the table at any point in time can be near instantaneous if you use the system stored procedure sp_SpaceUsed. And that can be used in a Trigger or Job actually.
    – J.D.
    Mar 12, 2021 at 12:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.