Support

Database performance issues for installations with large amounts of history data

Article ID: K00000475
Last Updated: April 6, 2015

Applies To

adTempus 4
adTempus 5

Summary

If your adTempus instance retains large amounts of history (for example, many jobs that retain thousands of history records), this may result in various database performance issues for adTempus.

Potential Symptoms

Common symptoms of these issues include:

Slow response in the Console when selecting a job
Frequent database error alerts and/or errors or warnings in the diagnostic logs reporting database deadlocks or timeouts. Note that adTempus retries failed database operations, and in most cases the database queries or updates succeed on a subsequent attempt. In this case no Alert is logged, but a warning will still be written to the diagnostic log.
adTempus may report an Alert with ID 5385 and an error message in the Windows Event Log indicating that the database is nearing the size limit for SQL Server express (see article K00000477)

Error Message Examples

Following are examples of the error messages that may appear in the diagnostic logs.

2015-03-26 15:45:11,576 [28] WARN  ArcanaDevelopment.adTempus.Server.ServerDataManager [(null)] - SQL Server error 1205, state 51: Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


2015-03-26 15:16:33,829 [MaintenanceThread] WARN  ArcanaDevelopment.adTempus.Server.ServerDataManager [(null)] - SQL Server error 1205, state 52: Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 65) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Resolution

First determine whether it is appropriate to be retaining large amounts of history data. The database scripts provided in article K00000476 can be used to determine how many instances and log messages are present for each job.

If you have an unexpected large amount of history data, first review the default Data Retention settings in the server options window and reduce those limits if appropriate. Then review the settings for individual jobs (on the General page of the Job Properties window) to fine-tune settings for individual jobs. For example, you may have a general requirement to retain history for one year. However, this requirement may not make sense for a job that runs repeatedly every day. Set the limit lower for such jobs.

Note: If you change retention settings, it may take up to 12 hours before adTempus starts removing old records, and the purge process may require several hours if large amounts of data need to be purged.

If you have determined that you need to be keeping large amounts of history data, additional database indexes may help improve performance. See the next section for more information.

SQL Server Express imposes a maximum database size, and retaining large amounts of history may cause you to reach this limit. See article K00000477 for more information on this limitation and on how to check your database size. You may need to migrate to a full SQL Server instance to support large-scale history retention.

Database Index Script

The following script contains the latest database indexes for adTempus. These indexes are applied when you create a new adTempus database or upgrade from an earlier major version (e.g., from 3.x to 4.x) but are not automatically applied when you perform a minor upgrade (e.g., from 4.0 to 4.1).

If you are experiencing problems that appear to be related to job history size, you can apply this script to your adTempus database. The script can safely be run against a database that may already have some or all of the indexes (redundant execution of the script will not cause any problems).

See article K00000385 for instructions on how to execute scripts against the adTempus database.

Index script last revised April 3, 2015.

IF NOT EXISTS (select * from dbo.sysindexes where name='IX_CapturedFile_ExecutionHistoryItem' and id = object_id(N'[CapturedFile]'))
CREATE NONCLUSTERED INDEX [IX_CapturedFile_ExecutionHistoryItem]
ON [capturedFile] ([ExecutionHistoryItemOID])
GO
IF NOT EXISTS (select * from dbo.sysindexes where name='IX_CapturedFile_ExecutionHistoryStep' and id = object_id(N'[CapturedFile]'))
CREATE NONCLUSTERED INDEX [IX_CapturedFile_ExecutionHistoryStep]
ON [capturedFile] ([ExecutionHistoryStepOID])
GO
IF NOT EXISTS (select * from dbo.sysindexes where name='IX_ConditionStatus_ExecutionHistoryItem' and id = object_id(N'[ConditionStatus]'))
CREATE NONCLUSTERED INDEX IX_ConditionStatus_ExecutionHistoryItem ON ConditionStatus (executionHistoryItemOID)
GO
IF NOT EXISTS (select * from dbo.sysindexes where name='IX_ConditionStatus_ExecutionHistoryStep' and id = object_id(N'[ConditionStatus]'))
CREATE NONCLUSTERED INDEX IX_ConditionStatus_ExecutionHistoryStep ON ConditionStatus (executionHistoryStepOID)
GO
IF NOT EXISTS (select * from dbo.sysindexes where name='IX_ConditionStatus_Condition' and id = object_id(N'[ConditionStatus]'))
CREATE NONCLUSTERED INDEX IX_ConditionStatus_Condition ON dbo.ConditionStatus	(conditionOID)
GO
IF NOT EXISTS (select * from dbo.sysindexes where name='IX_executionHistoryItem_RecordID' and id = object_id(N'[ExecutionHistoryItem]'))
CREATE NONCLUSTERED INDEX IX_executionHistoryItem_RecordID ON dbo.executionHistoryItem 	(recordID)
GO
IF NOT EXISTS (select * from dbo.sysindexes where name='IX_LogMessage_RecordID' and id = object_id(N'[LogMessage]'))
CREATE NONCLUSTERED INDEX IX_LogMessage_RecordID ON dbo.LogMessage 	(recordID)
GO

Software Updates

Arcana Development continues to make performance improvements to the adTempus software. If you are not already running the latest version of the software, check the Release Notes for the latest version to see if performance improvements have been made subsequent to your version, and consider upgrading.

Additional Assistance

For additional assistance reviewing or resolving performance issues, please open a support request to contact us.