Actions to improve performance in Microsoft CRM On Premise – 24 Checks

Performance is always a great concern for any CRM implementation and there are few key things need to consider when analyzing performance improvement. I thought it would be helpful if I summarize the basic key checks to be done to improve the performance in Microsoft CRM On premise deployment.

1. Check – Principal Object Access table size

2. Enable the ‘EnableRetrieveMultipleOptimization’ registry key

3. AsyncOperationBase table maintenance

4. Check if schedule maintenance jobs are failing and need to be monitored and rectified

5. CRM database – sharing memory resources with other databases on the SQL instance

6. Enable “Auto update statistics” settings for query optimization

7. Optimize CRM built-in maintenance jobs with Indexes

8. To make use of “ExecuteMultiple” in bulk data update/create activities

 9. Check if the “Business Unit” structure is implemented appropriately

10. Check for Higher Index Fragmentation in CRM Database

11. Settings to improve the AsyncService performance

Now lets see these in detail:

1. Check – Principal Object Access table size

One of the key reasons for performance issue in Microsoft CRM is the improper maintenance of its database which results in the huge number of records in POA table.

Proper actions need to be taken to clear the POA table as well as to control the growth of this table to get the system working smoothly.

Query to find the table count:

–Get the total number of shared records grouped by Entity
SELECT  EV.NAME AS [ENTITY NAME],COUNT(POA.OBJECTTYPECODE) AS [RECORDS COUNT]
FROM PRINCIPALOBJECTACCESS POA
INNER JOIN ENTITYLOGICALVIEW EV ON EV.OBJECTTYPECODE = POA.OBJECTTYPECODE
GROUP BY EV.NAME
ORDER BY 2 DESC

1. Clean-up Principal Object Access

In order to clean-up this table, the following actions should be made:

1. Schedule a Bulk Deletion Job which would delete all completed and cancelled ‘Workflow logs’
2. Schedule a Bulk Deletion Job which would delete all completed and cancelled ‘Process Sessions’
3. Execute Microsoft recommended SQL command to clear the records from AsyncOperationBase and POA tables https://support.microsoft.com/en-za/kb/968520

1. Control records growth in POA table

1. Set the option to delete workflow Log after the completion of Async plugins⁺ and in Workflows⁺
2. Change the custom relationship behaviour from ‘Parent’ to ‘Referential’ or equivalent for the most affected relationships

• ActivityPointer
• Annotation
• Incident

3. Build and schedule a weekly maintenance job to clear the completed and cancelled           workflow logs older than x months

2. Enable the ‘EnableRetrieveMultipleOptimization’ registry key

Details: http://support.microsoft.com/kb/2535245

Please note that enabling this will cause the queries to make use of temp tables

3. AsyncOperationBase table maintenance

Problem Statement:

The workflows in the Dynamics CRM organization are configured to log every successful execution. If there is no business need to audit on successful executions, the data logged should be avoided. Having huge number of records in AsyncOperationBase table will have impact on performance.

Resolution:

In order to maintain this table the following activities will be implemented:

• Assess Workflow Job Retention option for all workflows
• Schedule the Clean up task for cleaning the asynchronous table
• Develop a query which looks for record count in the asynchronous table.
• Review the automatic schedule job timings for clean up and change if required.

 

4. Check if schedule maintenance jobs are failing and need to be monitored and rectified

 

Problem Statement: If CRM maintenance jobs are not running in the system, this results in DB having huge number of orphaned and obsolete records which would affect the performance of the system.

Resolution:

There are schedule jobs running in the CRM app server which can be scheduled to run during the off-business hours.

Also ensure that the below listed maintenance jobs are running effectively in the system.

• Deletion Service
• Indexing Management
• Reindex All
• Cleanup Workflows

 

5. CRM database – sharing memory resources with other databases on the SQL instance

 

When the Dynamics CRM database(s) share resources with other databases, the performance will be impacted. Monitoring for usage of the machine vitals

(memory, disk IO, network, CPU) will help in determining the impact it is having on the performance on Dynamics CRM.

To ensure predictability, the Dynamics CRM database should not share resources

with other applications, databases or servers.

Resolution:

There should be plans to move non-CRM Databases to a different DB instance

as per the best practices.

 

6. Enable “Auto update statistics” settings for query optimization

This setting will check for indexes that require statistics update and update automatically and thus improve the performance

 

7. Optimize CRM built-in maintenance jobs with Indexes

Check if proper indexes are added in AsyncOperationBase table. If the index is missing in this table clean up activity will be delayed/failed

8. To make use of “ExecuteMultiple” in bulk data update/create activities

ExecuteMultiple will work much faster compare to the “Execute” method as it will process requests in a batches [upto 1000 requests in 1 batch]

 9. Check if the “Business Unit” structure is implemented appropriately

Ideally “Business Unit” in CRM should be the backbone of the security model and thus the focus on using CRM business unit should be on designing the proper security model rather than business’s Business model. Creating huge number of CRM Business Units would also create x times Security Roles. Which will have an impact.

 

For eg: if the security requirement doesn’t need to segregate information across mulple functional Bus within the organizations, it doesn’t necessarily need to create CRM Business Unit. A custom Business Unit entity can be used to serve the purpose and hence reduce the load in CRM Business Unit structure.

 

10. Check for Higher Index Fragmentation in CRM Database

Having more number of fragmented indexes with average fragmentation count higher than 20 will have huge impact on SQL Server performance

11. Settings to improve the AsyncService performance

Better usage of the AsyncService settings would improve the load on SQL Server as well as the Async Server

Eg: Setting lower values for AsyncItemsInMemoryHigh and AsyncItemsInMemoryLow

 

This blog has the settings in more details:

http://www.powerobjects.com/2016/08/08/crm-asynchronous-service-performance-settings/

 

12 – Max Degrees of Parallelism

This SQL setting controls how many parallel query plans are considered for a request. The theory behind this is that SQL can spend a little extra time up front looking for the most efficient query plan which will be offset by the efficiencies gained by taking an optimal route.  In practice, SQL will not beat the initial plan so we should tell it to not bother looking by setting this to 1.

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure ‘max degree of parallelism’, 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

END

Read more here: http://msdn.microsoft.com/en-us/library/dd979074.aspx

 

13 – DB Compatibility Level

You may have upgraded your SQL Server to the latest and greatest (and fastest) but did you remember to update the individual CRM database compatibility levels? This doesn’t happen by default and you may still be a few versions behind. Open up SSMS and take a peek at your DB settings to start taking advantage of the new query optimizer.

14 – SQL Max Memory

Most DBAs will do this during your initial install but things can be missed or hardware can change over time (particularly in a virtualized environment where RAM can fluctuate on demand). Adjust your SQL max memory settings to use as much available memory as possible while still leaving a few gigs for the OS.

Read more here: https://msdn.microsoft.com/en-us/library/ms178067.aspx

15 – Disable Tracing

You likely don’t have tracing enabled in your production environment but, if you had to troubleshoot an issue, it is possible that you had turned this on. Make sure you haven’t neglected to turn it back off.

https://technet.microsoft.com/en-us/library/hh699694.aspx

 

Client Side Checks:

 

Recommended Internet Explorer Settings for Microsoft Dynamics CRM Compatibility

1. Compatibility View – Add your CRM URL to the list; use *.yourdomain.com format to cover all possible subdomains or *.dynamics.com if online:

2. Pop-up Blocker – Enable pop-up blocker if it is not already on; add *.yourdomain.com format to cover all possible subdomains or *.dynamics.comdynamics.com if online to the exclusions list:

3. Trusted Sited – Under “Internet Security: Security Tab: Trusted Sites” add *.yourdomain.com format to cover all possible subdomains or *.dynamics.com if using CRM online

4. Trusted Sites – Under Trusted Sites: Custom Level – enable the option “Allow Display of Mixed Content”

5. Increase disk space for temporary internet files near to 250 MB

In Internet Explorer go to – Tools > Internet Options > General tab > Settings > Temporary Internet Files – Set Disk space for temporary internet files to 250 MB preferred.

This technique ensures that the cache has adequate space to take advantage of improved load times when making subsequent visits to the same page for the same files.

6. Set the Zoom setting to 100%

In Internet Explorer go to – Tools > Zoom > Select 100% or Press “Ctrl+0”

A zoom setting other than 100% will result in an increase in the page rendering time and hence slower CRM performance.

7. Uncheck – Delete browsing history on exit

In Internet Explorer go to – Tools > Internet Options > General tab, under Browsing history, verify that the Delete browsing history on exit check box is cleared to prevent deletion of temporary Internet files each time that the browser is closed. This will help in storing CRM passwords and improve the browsing speed. This may not be advisable on a public computer or where the security protocols require the browsing history to be deleted.

8. Select – Check for newer versions of stored pages – Automatically

In Internet Explorer go to – Tools > Internet Options > General tab > Settings > Temporary Internet Files – Set – Check for newer versions of stored pages – Automatically. This will help improve the browsing speed when you use the computer to visit the same CRM web pages frequently.

9. Uncheck – Do not save encrypted pages to disk

In Internet Explorer go to – Tools > Internet Options > Advanced tab > Settings > Security settings > clear or uncheck – Do not save encrypted pages to disk.

This is the standard setting unless it was modified.

As part of DynaTectice – Dynamics Practice as a service program, we do support our partners in performance monitoring, OnPrem to Online migration and governance solutions. If you are looking for solutions around performance monitoring, please feel free to contact us. Our experts will certainly help you!