We have a SQL Server solution that has a table dsStaging.Audit
that stores audit records created by a third party transactional database. We use these audits to synchronise CRUD operations from the third party system into our SQL database.
CREATE TABLE [dsStaging].[Audit](
[SyncExecutionId] [bigint] NOT NULL,
[AuditDataGuid] [nvarchar](56) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[AuditDateTimeZone] [datetimeoffset](3) NULL,
[AuditEventGroup] [nvarchar](56) NOT NULL,
[TransactionId] [bigint] NOT NULL,
[TransactionSequence] [int] NOT NULL,
.
...
.
CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED
(
[SyncExecutionId] ASC,
[TransactionId] ASC,
[TransactionSequence] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
When the audits are processed, I want to move the audit records into a separate table Processed.Audit
, ready for deletion after x days.
CREATE TABLE [Processed].[Audit](
[SyncExecutionId] [bigint] NOT NULL,
[AuditDataGuid] [nvarchar](56) NOT NULL,
[AuditDate] [datetime] NOT NULL,
[AuditEventGroup] [nvarchar](56) NOT NULL,
[TransactionId] [bigint] NOT NULL,
[TransactionSequence] [int] NOT NULL,
.
...
.
CONSTRAINT [PK_Processed_Audit] PRIMARY KEY NONCLUSTERED
(
[AuditDate] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
My main aim in moving audits out of staging and into processed is performance. I need to make sure that the staging table is locked for as short a time as possible, so that any unprocessed audits can be processed as quickly as possible (fewer audits in the staging table = much faster processing).
We're looking at around 1.5m audit records going through this process every hour in batches of about 10k.
The process for moving audits will fire roughly every 20-30 seconds. The process for deleting Processed.Audit
records will execute every hour and delete 1 hour's worth of audits from X days ago (typically around 7 days).
- Should I turn the
Processed.Audit
table into a clustered index?
Minimum version to be supported: SQL Server 2012 Standard Edition.