Note: There is an updated version of this post here

Microsoft 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 (for CRM 2011), Outlook, Office and SharePoint. If your 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. Here are some resources and tips that can be helpful in optimizing CRM's performance and maintaining your data. These tips don't 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 like those at ClickDimensions, who have incorporated optimization techniques in the code for our solution, 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 DBA. 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:

  2. If you’re running SQL Server 2008 R2, Microsoft has an additional document on some CRM performance improvements you can make in this PDF:
  3. Bulk Record Deletion: Some entities in CRM can collect a lot of data, resulting in large tables in the SQL Server database. If you 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 step 4 below for a list of ClickDimensions entities that you might consider periodically reducing old records for. 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 2011 and CRM Online have an easy way to start Bulk Record Deletion jobs that is built right into the UI. CRM 4.0 supports bulk record deletion too, but you’ll need a free tool (or you can develop your own based on a sample in the Microsoft CRM 4.0 SDK) to actually start the deletion job.

    For CRM 2011/Online:
    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 job. 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.

    For CRM 4.0:

    To create a bulk record deletion job for CRM 4.0, you can follow these steps (Make sure to have a good backup of your CRM database and a good understanding of how to create views in CRM. ClickDimensions does not provide support for the use of 3rd-party tools):

    • THIS STEP IS IMPORTANT: Create a system view for the entity you want to delete. The view should use filter criteria so it only displays records you wish to delete. To create a system view, go to Settings > Customization > Customize Entities, and open the entity you wish to create the view for. (I’ve listed some key entities below). Define the view so you are sure it only shows records that you wish to delete. You might use filter criteria such as “Modified On | On or Before | 10/1/2011”. Save the view as, for example, “Records Modified Before 10/1/2011”, and publish the View.
    • Download this tool from CRM MVP Tanguy Touzard:
    • Follow the instructions on Tanguy’s blog to connect to CRM and select the view you created in step ‘a’ above (I would not recommend scheduling this job for recurrence if you’re likely to need to update the view’s filter periodically as time goes on):
    • You will enter the system name of the entity and select the view. For example, in this screenshot, I’ve selected the Anonymous Visitor entity and the “Inactive Anonymous Visitors” view:
    • This will create a bulk deletion job in CRM. You can monitor the progress of this job directly in CRM by navigating to Settings > Data Management > Bulk Record Deletion. All the tool does is create the job. CRM will handle the rest. Repeat as necessary for other entities.
  4. Here 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 company. 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 pages that are important for your sales and marketing efforts. If you place the tracking script on a webpage that has a lot 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.

Note: This blog article was adapted from a KB article on the ClickDimensions support site,