Considerations for CRM and SQL Server Performance

Implementing a fully-integrated marketing automation solution like ClickDimensions can reveal areas of your CRM deployment that require optimization. Depending on your usage of different feature areas of ClickDimensions, the volume of both data and queries against the CRM system can increase significantly. While ClickDimensions has no direct interaction with the SQL Server in a CRM deployment, poor SQL Server performance will impact the entire CRM deployment as well as the performance of integrated systems like ClickDimensions.

Recommendations:

  1. Apply the latest update rollups for CRM and the most recent version of ClickDimensions.
    – CRM versions, updates and build numbers can be found on Microsoft’s Premier Field Engineering blog.
    – Sign up to be notified about new versions of ClickDimensions here.
  2. If you use CRM on-premise, make sure you have a regular maintenance plan established for your SQL Server. The maintenance plan should reorganize and rebuild indexes, update statistics, and release cleared space.
  3. Architect your CRM on-premise deployment to meet the needs of your organization’s usage and planned integrations, including such steps as using a multi-server deployment model to separate the web application and data tiers to separate servers. With SQL Server running on its own server(s), another option for optimization in high-volume environments is to configure SQL’s log files to reside on a separate physical disk from the data files to reduce disk contention for I/O operations. Additionally, if your SQL Server is virtualized, be sure that the data files are not sharing a SAN or other storage with other virtual machines.
  4. Implement the optimization techniques described by Microsoft in these whitepapers:
    (Optimizing CRM and SQL: http://www.microsoft.com/en-us/download/details.aspx?id=27139)
    (Two whitepapers on security modeling and customization considerations for performance: https://www.microsoft.com/en-us/download/details.aspx?id=45905)
    In particular, many CRM customers have seen significant performance improvements with the following two optimizations:
    – “EnableRetrieveMultipleOptimization” registry key for CRM (especially for CRM 2011 pre-UR10)
    – 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 and CRM 2013.
  5. Enable WCF compression for an on-premises CRM deployment: See the articles here and here for more information on the large improvements in bandwidth and performance that WCF Compression can permit.
  6. Bandwidth: ClickDimensions integration requires a high-speed internet connection. If you have CRM installed on-premise, a minimum 10Mbps connection is recommended (up/down). Greater volumes of email may require increased bandwidth for the CRM server’s internet connection.
  7. Consider data retention needs. It may be helpful to periodically clean out old data that is no longer relevant to your users. This will vary from company to company, but some general guidelines are discussed in an article on the ClickDimensions blog. To plan for database growth, use this very approximate rule of thumb: For each 1 million emails sent, expect 1.5-2 GB of additional storage space for your database. (This can vary on how emails are sent – either bulk emails or many individual emails from workflow or nurtures – and how much website traffic you are tracking, etc.)
  8. CRM limitations: Microsoft CRM 2011, 2013 and CRM Online have a built-in limitation that restricts the maximum number of records that can be returned for many types of queries to a total of 50,000. This is called the AggregateQueryRecordLimit. For CRM on-premises, this limit can be adjusted by your CRM partner or administrator using the CRM SDK, PowerShell, or a direct update to the OrgSettings table in the CRM database. If this limit is not changed, you will see errors when trying to send an email to more than 50,000 recipients. This setting cannot be changed in CRM Online.
    – See here for PowerShell instructions: http://msdn.microsoft.com/en-us/library/2a4061cd-e6b4-4672-8c44-20a27c523718#use_powershell_advanced
    – And here for documentation of this setting: http://msdn.microsoft.com/en-us/library/gg334634.aspx
  9. Use dynamic marketing lists cautiously. Dynamic marketing lists in CRM 2011 and CRM 2013 are very resource intensive in CRM compared to static lists. If you expect to use dynamic lists for large recipient lists, make sure your CRM database server is well-optimized.
  10. Limit the number of unsubscribe records you import into CRM from a previous vendor. When transitioning from a previous vendor, you may wish to retain the suppressed email addresses that your previous vendor no longer sends to (for example, if the email address is invalid). You can do this by importing these addresses in the ClickDimensions “Unsubscribe” entity in CRM. However, it is important to note that for bulk Email Sends, ClickDimensions will retrieve all of the unsubscribe records in your CRM in order to remove the unsubscribes from the list of recipients. If you have many thousands of unsubscribes or known bad email addresses that you wish to migrate into CRM from your previous email marketing vendor, you will get better performance by instead updating the field “Allow Bulk Email” to “Do Not Allow” directly on the Contact or Lead record, rather than creating new Unsubscribe records. If ClickDimensions must retrieve tens of thousands or hundreds of thousands of Unsubscribe records, this will add a lot of overhead and processing time to each Email Send.

NOTE: If you see SQL timeout warnings or errors in your CRM when using ClickDimensions, it is important to recognize that ClickDimensions does not have any direct interaction with SQL Server. Our application will surface errors and warnings that CRM returns to us as your CRM interacts with your SQL Server. These types of warnings and errors are an indication that the CRM deployment requires maintenance and optimization to handle large datasets.

Does ClickDimensions Recommend Specific Indexes for our on-premise SQL Server database?

No. ClickDimensions cannot recommend specific indexes, though you may be interested in our Entity Relationship Diagram in order to understand which entities our solution includes and how they are related with different features. ClickDimensions does not interact directly with SQL – only with CRM’s web services via the Microsoft SDK for Dynamics CRM/365. Microsoft has not provided a way to specify or include indexes in a managed solution file (the file we provide that you install in your CRM). Also, indexes are not always helpful to the performance of every operation and they should be implemented to match your organization’s specific usage of both CRM and ClickDimensions (in terms of both volume of database transactions and usage patterns). An experienced DBA should review your CRM’s database using tools like SQL Profiler and Database Tuning Advisor in order to determine which additional indexes are appropriate for your CRM system’s optimal performance.

Helpful Tool for On-Premises Performance Troubleshooting:

Microsoft’s Premier Field Engineering team for Dynamics released a tool that can be used to analyze performance issues in SQL Server for an on-premises deployment of Dynamics CRM, the Performance Analyzer for Dynamics (aka, DynamicsPerf).

 

About Matt

Director, Technical Solutions at ClickDimensions
Microsoft Dynamics CRM MVP
Author, The Microsoft Dynamics CRM 2011 Administration Bible