Microsoft Dynamics CRM is an amazing system that makes use of an incredible combination of software and hardware – everything from Windows Server, SQL Server, IIS, the .NET Framework, DNS, ADFS, Outlook, Office and SharePoint. If you're not using CRM Online, it can sometimes require a lot of skill and expertise to keep all these pieces humming along like a well-oiled machine.
ClickDimensions makes full use of the range of CRM's capabilities in order to bring an astounding level of analytical intelligence to your marketing efforts. But tracking your website traffic and email delivery statistics means bringing a lot of data into your CRM.
Below are some resources and tips that can be helpful in optimizing CRM's performance and maintaining your data. These tips not only apply to ClickDimensions, but to CRM in general.
1. Optimization whitepapers from Microsoft: These whitepapers cover a lot of territory. ClickDimensions makes use of just about every piece of the CRM application, so every piece of CRM can be put through its paces: the database, the servers, your internet connection and all points in between. Some of the information in these whitepapers is geared towards developers, but there are also a lot of practical instructions for optimizing the CRM database and infrastructure.
Some of the key optimizations you should consider include creating and maintaining indexes (see item #4 below for some of the ClickDimensions entities whose tables you might want to optimize), ensuring your SQL Server has sufficient RAM, and keeping transaction logs on a separate disk from the data. Make sure you understand the ramifications of any change you make in SQL Server, and if you're unsure, consult an experienced database administrator. CRM also has the ability to be deployed in multiple server configurations, spreading server roles across multiple machines. Depending on the size of your organization and your usage of CRM, you may get varying mileage from the tips that Microsoft provides in these whitepapers.
- For CRM 2013:
- For CRM 2015/2016:
2. If you're running SQL Server 2008 R2, Microsoft has an additional document on some CRM performance improvements you can make:
3. Bulk record deletion: Some entities in CRM collect a lot of data, resulting in large tables in the SQL Server database. If your SQL Server is not adequately optimized, users may notice slow responses when querying these entities through views, reports or other means. If the data is only valuable for a certain amount of time, it might make sense to periodically remove old records to keep the size of these tables in check. (See item #4 below for a list of ClickDimensions entities for which you might consider periodically deleting old records. As with anything related to CRM, make sure you understand how your business users make use of the data in CRM, and always make sure you have good, scheduled backups.) Fortunately, CRM has an easy way to start bulk record deletion jobs that is built right into the UI.
For CRM 2013/2015/2016:
Navigate to the Settings area and click on Data Management. Click the Bulk Record Deletion link and then click New to launch the Bulk Record Deletion wizard.
In the Bulk Record Deletion wizard, define the records you wish to delete based on criteria that makes sense for your business users. The interface is very similar to CRM's Advanced Find. In the screenshot below, I'm setting up criteria to remove Anonymous Visitor records that were created on or before a certain date:
Next, choose the date and time to start the bulk record deletion. If the criteria you defined in the previous step lend themselves to it, you can even schedule this as a recurring job. (It wouldn't make much sense in my example to create a recurring job, since the date in my example is fixed.) You can even tell CRM to email you when the job is completed. Depending on the number of records you are deleting, this may take a while to run.
4. Below is a list of some of the entities in CRM that may collect a lot of data, where you might consider removing some older records from CRM when they are no longer needed. Create system views with filter criteria that match your business requirements and think carefully about what the right data retention policy should be for your organization. Note that many of these entities are inter-related, so deleting an Anonymous Visitor record will delete its child Visit records and Page View records:
- Anonymous Visitor (cdi_anonymousvisitor)
- Visit (cdi_visit)
- Page View (cdi_pageview)
- Email Events (cdi_emailevent)
5. To reduce the amount of data flowing into CRM: Lastly, it always makes sense to evaluate the type and amount of data you are collecting in CRM. For example, your website statistics can drive a lot of value for your marketing efforts, but you may find it helpful to focus on key strategic and actionable statistics, such as landing pages, pricing pages, etc. Consider limiting the pages where you place the ClickDimensions tracking script on your website to just the pages that are important for your sales and marketing efforts. If you place the tracking script on a webpage that has a good deal of traffic but little value to your sales and marketing goals, you may end up with a lot of data in CRM that is not actionable and not worth retaining.
Written by Phil Drake, ClickDimensions Solution Success Engineer
