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.
- Generic SQL Errors
- SQL Deadlocks
- Slow Performance
Dynamics CRM Application Level Optimization Points
CRM Form Level Optimization: (Start with Worst Performing Forms)
- 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
- 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.
- 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
- Tabs, Sections and Fields not needed on the form should be removed. Remove unused fields analysing by CRM Data Detective
- Filter all queries in form level. don’t retrieve all fields
- Use Unexpanded Tabs and keep tabs as unexpanded wherever possible
- Remove on load function and do it in on change method wherever possible
- Minify JavaScript files to decrease the size of JS file
- Use Xrm.Utility.alertDialog() and .confirmDialog() as viable alternatives to window.alert()/confirm().Avoid use of modal dialogs.
- Remove unwanted or other web resources from form level wherever possible
- Try to minimize CRM service calls from CRM Form is possible.
- The OData and Fetch XML queries must be filtered and retrieve only specific data needed for the task
- Remove Ribbon Buttons which are not required
CRM Plugin, Custom ASP.NET Website & WCF Web Service & Batch JOBS Level Optimization
- 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.
- Minimize CRM service calls wherever possible to make the plugin faster
- Set filtering attributes on plugin registration tool and set plugin execution orders
- Remove unwanted plugins wherever possible
- Remove Async plugins wherever possible. In general lean towards synchronous execution over asynchronous to avoid system overhead.
- Move late bound string literals of entity and attribute names into constants
- Remove use of class level variables
- 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.
- Optimize expensive loops . use break in for loop wherever needed.
- Use For instead of For Each in performance-critical code paths
- Take care of infinite loops using depth wherever required
- 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.
- Walk throw on exception handling. Ensure plugins only use InvalidPluginExceptionExecution to report exceptions to the platform
- Limit the no. of columns in the CRM Views.
OOB & Custom Workflow Activities Workflow Optimization
- Enable delete on completion of OOB Workflows
- Remove unwanted workflows or dialogs from production system
- check if workflows and dialog log tables are cleaned up regularly or not
- Revisit Long Waiting Workflows and fins alternate solution if possible
- Check if AsyncOperation table is regularly cleaned up
- Check POA table is cleaned up
Registry Optimization
- 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 - 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
- Disable the email pop-up reminder to speed up load times
Name: DisablePendingEmailReminder
Type: DWORD
Value: 1 - Set a preferred Domain Controller to speed up AD checks
Name: PreferredDC
Type: String
Value: DC_Name - 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
- Find out the expensive queries from event viewer whose execution exceeds 10 second and optimize adding index or optimizing query.
- 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.
- 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.
- DMV Queries https://msdn.microsoft.com/en-us/library/ms187974.aspx for more analysis
- 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.
- CRM 2011 Maintenance Job Editor R2 to set the job run time to fit the business need.
- 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.
- 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 .
- Enable WCF compression for an on-premises CRM deployment.
- Deletion of Completed Workflows, Async table cleanup & Delete Orphaned POA Records
If I missed anything you can suggest in comment section.