Question / Problem:
Improving Reporting processing performance in KTA 7.5 and lower by disabling DropIndexesOnMq2Staging
Answer / Solution:
"DropIndexesOnMq2Staging" is an option in Kofax Reporting that determines whether indexes in the TotalAgility_Reporting_Staging database are dropped by running sp_drop_idx, and then recreated by running sp_create_idx as part of normal processing (each time the reporting task runs). It is better for performance to disable this setting, especially if trying to process a backlog of reporting data. This setting was enabled by default in KTA 7.5 and lower, but is already disabled by default in KTA 7.6 and higher. To disable DropIndexesOnMq2Staging on KTA 7.5 and lower use the following steps.
On each reporting server:
- Step the Reporting service.
- Set a System Environment Variable where name="DropIndexesOnMq2Staging" and value="0".
- Exact instructions on setting an environment variable can be different depending on the version of Windows, so please search for documentation for the version you are using as needed. (examples)
Then ensure that the indexes exist in the TotalAgility_Reporting_Staging database by running the following:
In most circumstances, if the reporting service was stopped normally, then the indexes will already exist and this command will complete instantly. However if they do not, then this will recreate the indexes.
When complete, restart each Reporting service.
Visibility in Reporting Logs
Evidence of this option is only visible in ETLApp_*.log files if the reporting ETL logs are set to “Information” or higher in C:\Program Files\Kofax\TotalAgility\Reporting\Kofax.CEBPM.Reporting.AzureETL.exe.config.
Then the ETLApp_*.log files will have occurrences of "Dropping indexes if existing ... " at which point the stored procedure “sp_drop_idx“ is run in the reporting staging database. Additionally there will be occurrences of "Creating indexes ..." at which point the stored procedure “sp_create_idx“ is run in the reporting staging database.
When the setting is disabled, these will no longer occur.