Hi,
I started cleaning my DB in CRM 2013 and found out that AsyncOperationBase is one of the biggest tables. It contains logs of workflows, bulk deletions etc. Our table had cca 30 mil records. Than I found the script for deleting the records here: https://support.microsoft.com/en-us/help/968520/performance-is-slow-if-the-asyncoperationbase-table-becomes-too-large .
Script finds 10,000 completed operations in AsyncOperationBase, then it deletes all connected logs in BulkDeleteFailureBase, WorkflowLogBase etc and in the end it cleans also AsyncOperationBase. And repeats it until all the records are deleted.
I added few lines to the script to display duration of one round and I was surprised that it took longer for each set of 10k logs after a while. E.g In the beginning I needed like 6 seconds for a round. After 30 rounds I needed like a minute. In my opinion it should be actually faster after every round because the connected tables have less and less records every time 10k records are processed.
My question:
1) why at the end of each iteration the temp table @DeletedAsyncRowsTable is not cleaned? Looks like same ids are cleaned again and again. So in first iteration 10k indexes are looked for in e.g WorkflowLogBase, than 20k, than 30k in next iterations
2) What is the purpose of the index creation in the beginning("CRM_AsyncOperation_CleanupCompleted")? I mean if almost all records in the table satisfy condition:
"OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3
AND StatusCode in (30, 32)"
isn't than the creation of index and maintaining the index while deleting making everything slower?
