I've noticed the maintenance job "Cleanup Workflows" was taking considerably more time to execute as my AsyncOperationBase table grows after some investigation I've noticed CRM needed a tweak to optimize this maintenance job.
The "Cleanup Workflows" maintenance job like the other CRM maintenance jobs are based on SQL storage procedures that are executed by the Asynchronous Maintenance service on a scheduled basis. This particular maintenance job was slowing down on this particular storage procedure: p_CleanupInactiveWorkflowAssemblies
which looks like this:
There is a select with a where clause on the AyncOperationBase table
This select takes around 3min o execute on my live environment (this was a lot longer on my uat and dev systems) with 3.5million records which I think is not that many records, this is because the column OwningExtensionId and OperationType are not indexed, out-of-the-box and up to RU14 it does not create the necessary indexes to make this query run faster you have to create it yourself.
I've created the following index:
and the query takes now 0seconds to be executed.
Hope this was helpful.
