Article Number
000039929
Applies To
Product(s): Archer
Version(s): All Versions
Primary Deployment: On Premises
Description
When investigating an issue within the database via a SQL trace such as in How to Run SQL Server Profiler Trace for a Specific Archer Job or other methods, it is found that the usp_validate_content_lock stored procedure is running slow with lots of blocking.
Cause
The primary cause for this proc running slow is likely tblLogicalTransaction table. This table is a heap table and these tables can leave large footprints of empty space when the heap table is used in high volume queues or tables that handle temporary transactional data. In either case, data is inserting new items, then read, and then delete.
The data in this type of table always adds the new data to the end of the table and allows for gaps and eventually large empty areas in the table.
The command below can be run to determine the amount of space used by the table:
EXEC sp_spaceused tblLogicalTransaction
Resolution
If the number of rows is 0 or just a few but the amount of reserved space is high as in the example above, then this table needs to be rebuilt and added to your regular maintenance.
If you do not want to do the rebuilds daily, then you will need to monitor the growth of the tables to determine how frequently the rebuild needs to be run.
It is not exactly known at what point the performance starts to degrade but the issue has been seen on a few hundred MB of reserved space on the tblLogicalTransaction table. It is best to keep this number low for ideal performance.
ALTER TABLE dbo.tblLogicalTransaction REBUILDBelow are other similar tables that could possibly run into the same issue so ensure that these tables are being frequently rebuilt if needed:
- tblAsyncJobsToCondense
- tblCalcErrorValue
If you do not want to do the rebuilds daily, then you will need to monitor the growth of the tables to determine how frequently the rebuild needs to be run.
It is not exactly known at what point the performance starts to degrade but the issue has been seen on a few hundred MB of reserved space on the tblLogicalTransaction table. It is best to keep this number low for ideal performance.
