The online index rebuild operation is an application that upgrades indexes in the background and converts them from a none compression state into a page level compression type. This operation updates indexes on the Events table in the databases on each of the monitored instance to become a page compressed type. This index upgrade shows considerable performance advantages and optimizes performance when viewing and managing event data.
The following 7 Indexes are included in the Events table of a monitored instance being audited:
- IX_Events_eventId
- IX_Events_eventCategory
- IX_Events_eventType
- IX_Events_databaseId
- IX_Events_appNameId
- IX_Events_hostId
- IX_Events_loginId
The operation is performed as an ONLINE operation (only for supported SQLServer versions) keeping the database table(s) available for users. The application checks the SQL Server version and then checks if the index compression type is set to page or none. On the basis of the compression type it performs the Index upgrade query ONLINE for compression to page type. The process then creates the non existing 7 indexes mentioned above, after creation it performs the compression on the 7 indexes of the event databases (for each instance added for auditing). For large repository, index rebuilds can take a significant amount of time. If you need to defer the rebuild, the SQL Compliance Manager update process will proceed and you can choose to rebuild the indexes later. For new instances, it creates the page compressed type indexes by default.
The index upgrade operation is skipped if the compression type is already of page type.
Manually run the Index rebuild operation
In a few SQL Servers versions (such as the STANDARD edition), the index creation with the ONLINE property is not supported and therefore the operation results in a failure message displayed to the user, instructing to run the application manually without the ONLINE property.
Before updating the indexes, ensure the selected database has sufficient free space to accommodate these changes. For example, if the current database is 1MB in size, the updated database may grow to 2 MB. In this case, the update process would require 1MB of free space.
User can run the utility manually with additional parameters to build the indexes without the ONLINE option as follows:
<installdir>\SQLcomplianceIndexUpgrade.exe "server=serverName; database=SQLcompliance; integrated security=SSPI; Connect Timeout=30;” “ONLINE=OFF”
The second parameter “ONLINE=OFF” is an optional parameter and is to be used only when the index update operation cannot be performed with the ONLINE option.
This operation should only be done during a downtime, since the index creation will acquire a lock on the Events table and the auditing will not work in that duration.
When the utility is run manually the Index rebuild operation does not occur as an ONLINE operation.