Implementing a fully-integrated marketing automation solution like ClickDimensions can reveal areas of your Dynamics deployment that require optimization. Depending on your usage of different feature areas of ClickDimensions, the volume of both data and queries against the Dynamics system can increase significantly. While ClickDimensions has no direct interaction with the SQL Server in a Dynamics deployment, poor SQL Server performance will impact the entire Dynamics deployment as well as the performance of integrated systems like ClickDimensions.
Recommendations
- Apply the latest update rollups for Dynamics and the most recent version of ClickDimensions. Sign up to be notified about new versions of ClickDimensions here.
- If you use Dynamics 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.
- Architect your Dynamics 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.
- Implement the optimization techniques described by Microsoft in these whitepapers: (Optimizing Dynamics 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 Dynamics customers have seen significant performance improvements with the following two optimizations: - "EnableRetrieveMultipleOptimization" registry key for Dynamics (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.
- Enable WCF compression for an on-premises Dynamics deployment: See the articles here for more information on the large improvements in bandwidth and performance that WCF Compression can permit.
- Bandwidth: ClickDimensions integration requires a high-speed internet connection. If you have Dynamics installed on-premise, a minimum 10Mbps connection is recommended (up/down). Greater volumes of email may require increased bandwidth for the Dynamics server's internet connection.
- Consider data retention needs. It may be helpful to periodically clean out old data that is no longer relevant to your users. 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.)
- Use dynamic marketing lists cautiously. Dynamic marketing lists are very resource intensive in Dynamics compared to static lists. If you expect to use dynamic lists for large recipient lists, make sure your Dynamics database server is well-optimized.
- Limit the number of unsubscribe records you import into Dynamics 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 Dynamics. However, it is important to note that for bulk Email Sends, ClickDimensions will retrieve all of the unsubscribe records in your Dynamics environment 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 Dynamics 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 Dynamics environment 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 Dynamics returns to us as your environment interacts with your SQL Server. These types of warnings and errors are an indication that the Dynamics 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. ClickDimensions does not interact directly with SQL - only with Dynamics' web services via the Microsoft SDK for Dynamics 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 Dynamics environment). 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 Dynamics and ClickDimensions (in terms of both volume of database transactions and usage patterns). An experienced DBA should review your Dynamics environment's database using tools like SQL Profiler and Database Tuning Advisor in order to determine which additional indexes are appropriate for your Dynamics environment's optimal performance.