PROBLEM:
When I log into my server, I get a pop-up that I need to rebuild my Audit tables to improve performance? How do I go about this?
SOLUTION:
The solution to this is to run a reapply schema. A reapply schema rebuild a variety of the indexes in the database and also adds indexes if they are missing. That pop-up can appear if there are indexes missing on the audit table primarily because the Audit tables tend to be one of the largest tables in the database.
Simply go to the System tab > Configure page > and click on 'Reapply Schema'
You can also rebuild these indexes through SQL Management studio by running the query below.
USE [ksubscribers]
GO
DROP INDEX [auditRsltApps_applicationName_idx] ON [dbo].[auditRsltApps]
DROP INDEX [auditRsltApps_agentGuid_idx] ON [dbo].[auditRsltApps]
DROP INDEX [IX_auditLastAudit] ON [dbo].[auditRsltApps]
DROP INDEX [IX_baseAuditAgent] ON [dbo].[auditRsltApps]
DROP INDEX [IX_lastAuditAgent] ON [dbo].[auditRsltApps]
go
CREATE NONCLUSTERED INDEX [auditRsltApps_agentGuid_idx] ON [dbo].[auditRsltApps]
(
[agentGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [auditRsltApps_applicationName_idx] ON [dbo].[auditRsltApps]
(
[applicationName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_auditLastAudit] ON [dbo].[auditRsltApps]
(
[lastAudit] ASC,
[agentGuid] ASC
)
INCLUDE ( [flags]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_baseAuditAgent] ON [dbo].[auditRsltApps]
(
[baseAudit] ASC,
[agentGuid] ASC
)
INCLUDE ( [applicationName],
[flags]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_lastAuditAgent] ON [dbo].[auditRsltApps]
(
[lastAudit] ASC,
[agentGuid] ASC
)
INCLUDE ( [applicationName],
[flags]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO