Monitoring your AsyncOperationsBase Table

One of my customers started having huge performance issues related to the database.  We were also seeing SQL Timeout errors in the Application Event log and sometimes within the CRM client ( web or Outlook ).

After verifying that the database checked out without errors, we traced down the problem to the Microsoft CRM Asynchronous Processing Service ( Async Service, for short ).

I turns out that the AsyncOperationsBase table was huge:

Data space was 944,789 MB

Index space is 90,258 MB

Some digging produced the following Microsoft KB Article:

The AsyncOperationBase and WorkflowLogBase tables grow very large and performance issues occur when you use many workflows in Microsoft Dynamics CRM 4.0

It describes a hotfix, which is included in Hotfix Rollup 3 for CRM 4.0, that allows you to add a registry key to the CRM server to remove old, successfully completed bulk operations.

Within that article, another article is mentioned:

Performance is slow if the AsyncOperationBase table becomes too large in Microsoft Dynamics CRM 4.0

Which gives you a SQL script to removed these old records.  After running the script in the development environment to make sure it ran correctly we ran it in production.  This resulted in the size of the AsyncOperationsBase table:

Data space was 182.063 MB

Index space is 29.813 MB

Quite a difference, huh?

Anyway, if your server starts slowing down, you may wish to give these two articles a good read-through.