Applies to
Summary
This article explains how to reduce SQL audit data repository size and improve performance through index maintenance.
Description
During regular operational usage of the SQL audit central repository database, it is expected that the relevant indexes become fragmented which will result in decreased database perforance and, consequently, reduce the reading i.e. reporting performance. To remedy the situation, indexes would require some maintenance, which was elaborated within the article: SQL index maintenance. To avoid manual intervention, the ApexSQL Audit application has a built-in repository index maintenance mechanism, and further in this article, it will be explained how to use and configure this mechanism.
Configuring the index maintenance
To enable and configure the index maintenance, open the Maintenance wizard window, and pick the Repository maintenance schedule item:
This will bring the wizard to the next step where the repository maintenance scheduler can be enabled and configured:
The scheduler will allow setting the time frame and the frequency on which the index reorganization should be conducted. It is recommended to set these parameters based on average central repository usage. For example, if the central repository is collecting daily only a few hundred to a few thousand audited events and the reports are being generated once per month or quarterly, it is safe to set the schedule to the Monthly frequency and to Start at any time of day and finish reorganizing indexes until Completed:
On the other hand, if the amount of collected events exceeds 2 million per day and the repository grows several gigabytes in size daily, it would be recommended to set the scheduler to trigger the index reorganizing Weekly (at least once or twice a week), and configure it to Start at during low SQL traffic hours (i.e. “overnight”). On the off chance that the index reorganization cannot be finished before the low SQL activity period ends, it is recommended to set the time when the process should End at, since it cannot be stopped manually, keeping in mind that it will leave partial index fragmentation and finish it on the next maintenance occurrence:
When the index maintenance starts, one of two possible activities will be triggered, based on the level of index fragmentation. Either the indexes will be reorganized, or, if fragmentation exceeds 50%, the indexes will be rebuilt. Rebuilding indexes will be conducted in the offline mode (the tables will be locked for writing), except in case the repository is attached to the SQL Server Enterprise edition where rebuild can and will be conducted in online mode. However, although it will take a short period for that, no auditing data will be dropped as the data remains saved in packages on the file system in case the repository is in such maintenance mode.
If the scheduler is set, the Next button will lead to the final wizard step where the schedule configuration summary is shown and can be reviewed before confirming. Click Finish to confirm the settings:
A reminder to currently active schedule configuration for the index maintenance and the status on its recent activity can be found under the Status tab when the Central instance item in the Servers panel is in focus:
Setting the index maintenance properly can result in SQL audit repository database performance improvement and significantly reduce the time needed to generate reports.
