Deal with Slow Performed CRM

Hello Friends !!! Welcome to another CRM article which will explain how to deal with slow performance in Dynamics CRM.

Check out Microsoft white paper on optimization for Dynamics CRM 2011.

For CRM 2015 & 2016 click here to download from Microsoft site.

Some important whitepapers you can find here

Troubleshoot the slow performed CRM forms using performance analyser.

We will focus below three points to analyse all possible resolutions.

  1. Generic SQL Errors
  2. SQL Deadlocks
  3. Slow Performance

site-speed.jpg

Dynamics CRM Application Level Optimization Points

CRM Form Level Optimization: (Start with Worst Performing Forms)

  1. Refactor Busy JavaScript on load function – Use fiddler to analyse – This analysis should start with worst performing CRM forms like Work-order, Contract, payment, Quote
  2. Too many sub grids on form – Form load time can be saved by putting the grid in an unexpanded tab, this will not load the grid on the form load but will load it when the user expands the tab.
  3. Lots of FetchXML/OData Queries. ALWAYS filter your OData queries to retrieve only the fields you need. Group OData queries to avoid multiple OData queries. Revise queries to optimize by DBA
  4. Tabs, Sections and Fields not needed on the form should be removed. Remove unused fields analysing by CRM Data Detective
  5. Filter all queries in form level. don’t retrieve all fields
  6. Use Unexpanded Tabs and keep tabs as unexpanded wherever possible
  7. Remove on load function and do it in on change method wherever possible
  8. Minify JavaScript files to decrease the size of JS file
  9. Use Xrm.Utility.alertDialog() and .confirmDialog() as viable alternatives to window.alert()/confirm().Avoid use of modal dialogs.
  10. Remove unwanted or other web resources from form level wherever possible
  11. Try to minimize CRM service calls from CRM Form is possible.
  12. The OData and Fetch XML queries must be filtered and retrieve only specific data needed for the task
  13. Remove Ribbon Buttons which are not required

CRM Plugin, Custom ASP.NET Website & WCF Web Service & Batch JOBS Level Optimization

  1. Optimize the queries and fetchXML, Query Expressions. Only retrieve the minimum necessary attributes for a business operation to perform its task. Identify if Index is required with DBA.
  2. Minimize CRM service calls wherever possible to make the plugin faster
  3. Set filtering attributes on plugin registration tool and set plugin execution orders
  4. Remove unwanted plugins wherever possible
  5. Remove Async plugins wherever possible. In general lean towards synchronous execution over asynchronous to avoid system overhead.
  6. Move late bound string literals of entity and attribute names into constants
  7. Remove use of class level variables
  8. Ensure service insert or update calls do not reuse existing entity objects and instead use new entity objects with only the required fields for the operation.
  9. Optimize expensive loops . use break in for loop wherever needed.
  10. Use For instead of For Each in performance-critical code paths
  11. Take care of infinite loops using depth wherever required
  12. Explicitly call the close or dispose methods of disposable objects created during execution before they fall out of scope to minimize the life of the object in memory. This applies to things such as database connections, message queue handles, file handles, etc. Close the external service connections in finally step.
  13. Walk throw on exception handling. Ensure plugins only use InvalidPluginExceptionExecution to report exceptions to the platform
  14. Limit the no. of columns in the CRM Views.

OOB & Custom Workflow Activities Workflow Optimization

  1. Enable delete on completion of OOB Workflows
  2. Remove unwanted workflows or dialogs from production system
  3. check if workflows and dialog log tables are cleaned up regularly or not
  4. Revisit Long Waiting Workflows and fins alternate solution if possible
  5. Check if AsyncOperation table is regularly cleaned up
  6. Check POA table is cleaned up

Registry Optimization

  1. You can control the size of the Workflow table by deleting completed Workflows with a registry key, this is a nice way to improve performance on the database side, however this can only be applied if you don’t need the workflow history.
    Name: AsyncRemoveCompletedWorkflows
    Type: DWORD
    Value: 1
  2. Improve SQL Performance EnableRetrieveMultiple
    Type: DWORD
    Value: 2 (Decimal)
    Fixes a number of issues around the SQL tempdb database and improves performance. http://support.microsoft.com/kb/2535245
  1. Disable the email pop-up reminder to speed up load times
    Name: DisablePendingEmailReminder
    Type: DWORD
    Value: 1
  2. Set a preferred Domain Controller to speed up AD checks
    Name: PreferredDC
    Type: String
    Value: DC_Name
  3. Configure the following registry key on the Asynchronous servers in order to improve performance and avoid dead locks.
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSCRM
    Name: AsyncDBAppLock
    Type: DWORD
    Value: 1
    For more information on the above registry key refer to the following KB: http://support.microsoft.com/kb/2249156Database changes: On the MSCRM_CONFIG database, DeploymentProperties table you can find a few fields starting with Async, these fields are used to control the Asynchronous service behaviour tweaking it to your exact needs. Important: changes to these fields should be done always on a dev/test environment before applying it to production and always backup your database or table before changing it.
    Note: If your AsyncOperation table has never been cleaned up before, you may experience excessive locking during the cleanup process. You will want to schedule your initial cleanup during a maintenance window. If you do experience excessive locks, and you may need to schedule it during a down time maintenance window when you can run the database in single-user mode during the initial cleanup and/or stop the AsyncService during the initial cleanup period.Additionally, if once you schedule a regular cleanup, you will want to monitor for excessive locking and take action as appropriate for your environment.

    Dynamics CRM SQL DB Level Optimization Points

  4. Find out the expensive queries from event viewer whose execution exceeds 10 second and optimize adding index or optimizing query.
  5. Check Recent Long Running Queries in activity monitor of SQL and take action. Pay special attention to the number of executions, if it runs a lot, there might be a real benefit to building an index if one doesn’t already exist.
  6. In all cases, if you copy the suspect query and paste it to a new query window in SQL Server and select “Display Estimated Execution Plan”. You just might get lucky and see some green text like the sample below: to find missing index and we can go for adding index if required.
  7. DMV Queries https://msdn.microsoft.com/en-us/library/ms187974.aspx for more analysis
  8. Microsoft officially recommends that indexes be reorganized when fragmentation reaches 5-30% and they should be rebuilt when fragmentation is > 30%. These are strictly guidelines and in some cases, if there aren’t in excess of 1000 pages in your indexes a rebuild may not have an appreciable impact. It is important to note however that a REBUILD will automatically Update Statistics, a reorganize does not.
  9. CRM 2011 Maintenance Job Editor R2 to set the job run time to fit the business need.
  10. Make sure your indexes are being reorganized or rebuilt and statistics are getting updated (done automatically if you do a rebuild). The OOTB indexes are pretty good but they need to be updated periodically to be as efficient as possible.
  11. Setting the Max Degrees of Parallelism for SQL ServerBoth of these are discussed in Microsoft’s white paper and are applicable to on-premises deployments of both CRM 2011 .
  12. Enable WCF compression for an on-premises CRM deployment.
  13. Deletion of Completed Workflows, Async table cleanup & Delete Orphaned POA Records

If I missed anything you can suggest in comment section.