-2

Currently we have an AuditLog table that holds over 11M records. Regardless on the indexes and statistics any query referencing this table takes a long time. Most reports don't check for Audit records past a year but we would still like to keep these records. Whats the best way to handle this?

I was thinking of keeping the AuditLog table to hold all records less than or equal to a year old. Then move any records greater than a year old to an AuditLogHistory table. Maybe just running a batch file every night to move these records over and then update the indexes and statistics of the AuditLog table. Is this an okay way to complete this task? Or what other way should I be storing older records?

The records brought back from the AuditLog table hit a linked server and check in 6 different db's to see if a certain member exists in them based on a condition. I don't have access to make any changes to the linked server db's so can only optimize what I have which is the Auditlog. Hitting the linked server db's uses up over 90% of the queries cost. So I'm just trying to limit what I can.

user3199317
  • 29
  • 6

1 Answers1

3

First, I find it hard to believe that you cannot optimize a query on a table with 11 million records. You should investigate the indexes that you have relative to the queries that are frequently run.

In any case, the answer to your question is "partitioning". You would partition by the date column and be sure to include this condition in all queries. That will reduce the amount of data and probably speed the processing.

The documentation is a good place to start for learning about partitioning.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I agree that partitioning is the answer. I just wanted to add one thing. The OP mentions indexing their AuditLog table. Indexing makes reads faster but it also makes inserts slower. A log table is probably going to have a lot of inserts and relatively few reads. So they should not add indexes unless they really, really need them. It wont hurt their reports at all but might make the processes that feed into their log slower. – David Cram Feb 10 '17 at 19:30
  • I had the almost exact same scenario, except I had two linked audit tables, one about 100M rows and I didn't need to keep the old records. Deleting audit records older than a certain time was just painfully slow. It turned out the be about 80x faster to rename the table to auditlog_history, recreate the audit log table, insert back into it from the renamed table, then truncate the auditlog_history table. In your case you could just skip the truncate. Of course you will have to drop and recreate any foreign keys or indexes. – Robert Sievers Feb 10 '17 at 20:19
  • The records brought back from the AuditLog table hit a linked server and check in 6 different db's to see if a certain member exists in them based on a condition. I don't have access to make any changes to the linked server db's so can only optimize what I have which is the Auditlog. Hitting the linked server db's uses up over 90% of the queries cost. So I was just trying to limit what I can. Thanks I will look into partitioning. – user3199317 Feb 10 '17 at 23:07