How to Resolve Dynamics CRM Performance Issue

How to Resolve Dynamics CRM Performance Issue

Scenario: 

We were receiving day to day complaints from CRM users about the performance of Live CRM.  One of the reasons behind these performance issues were system job records, we had  3.7 million records in system job AsyncOperationBase table. This was causing large backups and slowness of CRM.

Database: <Orgname>_MSCRM

Table: Dbo.AsyncOpertationBase

How to Resolve Dynamics CRM Performance Issue

System Jobs in Dynamics CRM:

How to Resolve Dynamics CRM Performance Issue

Error message while Advance find:

 

How to Resolve Dynamics CRM Performance Issue

Same Error when we run bulk deletion to clear system jobs:

How to Resolve Dynamics CRM Performance Issue

Action Plan:

Perform this job out of hours or at weekends when we run our performance scripts, it can affect the performance of server.

  1. Backup the CRM database
  2. Shrink the Database by right clicking and selecting tasks, shrink, database:
  3. Then improve performance by creating some indexes for the clean-up query.CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted

    ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])

    GO
    CREATE NONCLUSTERED INDEX CRM_WorkflowLog_AsyncOperationID ON [dbo].[WorkflowLogBase] ([AsyncOperationID])

    GO
    CREATE NONCLUSTERED INDEX CRM_DuplicateRecord_AsyncOperationID ON [dbo].[DuplicateRecordBase] ([AsyncOperationID])

    GO
    CREATE NONCLUSTERED INDEX CRM_BulkDeleteOperation_AsyncOperationID ON [dbo].[BulkDeleteOperationBase]

    (AsyncOperationID) GO

  4. Stop the Microsoft CRM Asynchronous Processing Service in the Server Services, found under the Administrator tools / Services
  5. Set Simple Database Recovery. Right click the database and select Properties / Options / Recovery Model to simple
  6. Run Script provided by Microsoft https://support.microsoft.com/en-us/kb/968520
  7. Reset the database to Full Recovery Mode, reversing the step we took in step 5.
  8. Shrink the database again exactly as we did in step 2.
  9. Start service Microsoft CRM Asynchronous Processing Service

If you need help with your Dynamic CRM or SharePoint  please drop us an email usman@Dynamics 365 Solutions.com or contact us.

If you like this post please click LIKE and visit my sponsors thanks !!

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *