At a loss about how to solve Dynamics CRM performance issues and weird faults you can’t fathom out?
By Jamie Wilbraham and Graham Hill @ Metisc
This article covers a potential solution to inconsistent problems our clients have experienced that have had quite an impact on Dynamics CRM; including the CRM in Outlook, the CRM through a Browser, Workflows and System Jobs.
In all cases where clients were having issues, we had tracked through the Microsoft Blogs and Forums, tried different solutions, logged operations and reviewed event logs, involved Microsoft on occasions and generally had a frustrating time trying to solve the issues.
We were struggling to identify the faults and overcome them, rebooting the CRM Servers seemed to provide a temporary solution; when we discovered a workaround that seems to provide a permanent solution for many of our clients.
It’s simple, and easy to accomplish. Also has some side benefits of reducing the size of the CRM database (for those using CRM online this may be a cost benefit).
So what have we been doing?
- In many CRM installations when we started having what I would call general instability, we checked out the size of the system logs and of the audit logs (I said it was simple).Reviewing the CRM systems logs can be done by a user with Administrative privileges, to review the system logs, please go to Settings>> System Jobs
In the logs, it shows 5000 records by default. Scroll through these records to get an idea of how many records are be being created on a normal day (rough estimate is OK with some simple maths).
Multiply the number of workflows created in a normal day by about 220 work days a year (I know the workflows keep running on weekends, but just getting a rough guess here). And you have a very rough idea of how many entries you will create in a typical year. This number can be huge!
If you are technically minded, you can see the number of records in the logs using the following Transact SQL statement connected to the CRM database that holds the logs. In a SQL Management Console, enter this command once you select your CRM tenant database and open a query window>
SELECT count(*) FROM [AsyncOperationBase]
When it’s completed you can see at the bottom of the query screen how long it took. Don’t be surprised if your CRM Database/SQL server response time is slow for a short time whilst this works behind the screen. As a rough example, in one case we worked through, a standard SQL query on the Account entity table returned all the rows in about 4 seconds, but the log entity table query took two and a half minutes to get a count. This is an indication why we decided to remove as many of these records as possible.
We found that for large or small clients, if there was a lot of activity around workflows and auditing, the table size was in the hundreds of thousands or millions of records. Now in itself, the number of records with todays current server systems should not cause an impact. But what we found in all clients we removed old log entries from, was that that the size of the database dropped considerably, the speed of the CRM system improved and the weird instability disappeared in most of the cases.
But be careful with this method as some clients may need these log records for legislative or tracking needs.
So be cautious about removing log entries from the CRM system log history, another safer option is to copy the system log entries to a secondary table or database to ensure you comply with the record retention policies in your organisation or CRM system before deleting them. If you are running on CRM Online you don’t have access to the CRM logs, so a tool may be required to copy these records out to another database before running the CRM utility to delete the log entries.
To delete the entries from within CRM we need you to go to Settings >> Data Management >> Bulk Record Deletion.
Select the new option from the top of the menu, and create the bulk deletion job using the wizard, we suggest a criteria to use in the following screen shot.
- Please remember to select the “System Jobs” as the look for value in the top dropdown.
- The criteria can be updated to match your particular requirements, the Status Reasons used in this example is just a suggested starting point and works for most of our clients leaving any problems/errors untouched.
- Once you have finished with the selection criteria, you have the option to set this deletion to automatically execute every X days.
Once the Bulk Deletion Job is running, for those CRM On Premise users, you can monitor the progress of the deletion, by running the SQL query described above (SELECT count(*) FROM [AsyncOperationBase]) where you can see the number of records within the log reduce in real time as they are removed by CRM.
Speed and Other Improvements
The CRM response time/speed improved noticeably regardless of CRM being accessed from a browser or Outlook CRM Plugin; Outlook stopped randomly disconnecting; functions that erratically failed stopped failing; workflows that were misbehaving started working as we expected; basically we ended up with a well behaved Dynamics CRM system.
A great example of how this impacted one client; they began to see performance degradation in CRM and the odd email would error when tracking to the opportunity. This progressed to the Outlook connector timing out tracking emails, which progressed further to a general slowdown of the overall CRM performance from the web interface, what made it even more of a puzzle was it appeared randomly and would stop happening after about 5-10 minutes.
This made it hard to diagnose the issue initially, and it got worse with the CRM Asynchronous Server Service stopping responding, it was still running but not responding to request from Outlook or the web interface. The temporary solution was to restart the service. Initially this happened every monthly, but after a few months it become a weekly occurrence, and was starting to get to the point of daily restarts.
After we cleaned up the logging (about 1.5 million records, which to SQL is nothing really) and set it to only hold 2 months’ worth of unimportant logs, the server performance went back to normal, it has remained that way for the past 3 months and hasn’t required any CRM server restarts (except for one Server Restart to apply Microsoft server patches) which made a very pleasant change.
Now this isn’t a panacea to everything, but from practical experience, it does seem to be an effective strategy when all else fails (although we can’t put an exact reason on it) where all the other logical, suggested and recommended fixes failed, a reasonably simple left field idea has come through.
And it may not work for all clients and customers, but hey, if you are pulling your hair out with stuff you just can’t get resolved and don’t understand, it could be worth a try. And if you get a benefit from doing this, put it in your monthly processes and strip out/delete all system logs regularly.