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 appropriately disabled by default in KTA 7.6 and higher.
Resolving a timeout error
Because this setting is on by default in KTA 7.5 and earlier, it is possible that creating the indexes can take long enough so that a timeout occurs. In the logs it would look like this:
09/11/2019 06:24:42.230 AM Reporting Information: 4 : Requested empty EnvVariable 'DropIndexesOnMq2Staging', using default value of '' 09/11/2019 06:00:44.174 AM Reporting Information: 4 : Dropping indexes if existing ... 09/11/2019 06:00:44.174 AM Reporting Information: 4 : OK ... [other activities in the logs…] 09/11/2019 06:03:34.459 AM Reporting Information: 4 : Requested empty EnvVariable 'DropIndexesOnMq2Staging', using default value of '' 09/11/2019 06:03:34.459 AM Reporting Information: 4 : Creating indexes ... 09/11/2019 06:13:35.755 AM Reporting Error: 6 : Could not execute the Staging2Wh data task, because an exception has occured EventID:3 09/11/2019 06:13:35.755 AM Reporting Error: 6 : Exception: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. 09/11/2019 06:13:35.755 AM Reporting Error: 6 : Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Kofax.Reporting.ETLAgent.Core.DataAccess.SqlDataProvider.ExecuteSP(String sql, IDataParameter paramList, Int32 cmdTimoeout) at Kofax.Reporting.ETLAgent.Core.Packgage.Staging2WhPackage.ProcessData(IDBMSProvider whProvider, IDBMSProvider stagingProvider, IDBMSProvider etlDBProvider, IDBMSProvider bulkStagingProvider, ETLTaskHost& etlHost, ExtractDataTaskHost extractHost, Int32& etlJobKey, RecordETLTask& taskLog, ExtractActionFactory extractFactory) at Kofax.Reporting.ETLAgent.Core.Packgage.Staging2WhPackage.Execute()
When a timeout like this has occurred, the important thing to note is that the indexes have been dropped but have not been able to be recreated, so the tables are currently in a state where they have no indexes. This means that it isn’t enough to just disable the setting. We must also recreate the indexes.
On each reporting server:
- Stop the Reporting service and wait until there are no longer any running instances of Kofax.CEBPM.Reporting.AzureETL.exe.
- Set a System Environment Variable where name="DropIndexesOnMq2Staging" and value="0".
- Exact instructions on setting a system 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)
If the system is currently experiencing a timeout trying to create the indexes, then they need to be recreated manually. These steps just recreate the indexes if they don't exist, so it is preferable to follow them if there is any uncertainty.
To do this on an on-premise system, run the following on the TotalAgility_Reporting_Staging database:
In an On-Premise Multi-Tenant system, this would need to be run against the specific tenant databases. Replace TenantName with the actual tenant name in the below script. Note that this requires the connecting account have the IMPERSONATE permission, and this is needed because the stored procedures act on the default schema of the connecting user:
--Replace TenantName with the actual name of the tenant EXECUTE AS USER = 'TenantName_reportingstagingdev'; EXEC [reportingstagingdev].[sp_create_idx] REVERT EXECUTE AS USER = 'TenantName_reportingstaginglive'; EXEC [reportingstaginglive].[sp_create_idx] REVERT
When complete, restart each Reporting service.
Visibility in Reporting Logs
Other than if a timeout is occurring, 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.