When investigating the issue within the database via a SQL trace such as this article or other methods, it is found that the usp_validate_content_lock stored procedure is running slow with lots of blocking.
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.
This command can be run to determine the amount of space used by the table.
EXEC sp_spaceused tblLogicalTransaction
If the number of rows is 0 or just a few but the amount of reserved space is high like the example above, then this table needs to be rebuilt and added to your regular maintenance.
ALTER TABLE dbo.tblLogicalTransaction REBUILD
Here are some other similar tables that could possibly run into the same issue so ensure that they are being frequently rebuilt if needed.
The rebuild commands have been added to the usp_reindex script. This script is called by the maintenance jobs that are outlined here. If you do not want to do the rebuilds daily, you will need to monitor the growth of the tables to determine how frequently the rebuild needs to be run. We do not know at what point the performance starts to degrade but have seen the issue on a few hundred MB of reserved space on the tblLogicalTransaction table. It is best to keep this number low for ideal performance.