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
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.