In a recent XRM project for a client who uses CRM to manage grants and grant distributions, we had created several custom entities with many relationships among them as well as with core system entities. After loading approximately a half million records across 6 custom entities, we discovered that we could no longer delete attributes or entities using the customization administration settings within the CRM client.  The error we received was generic as indicated below.

After a bit of diagnostics, a handy-dandy SQL Trace, and some SQL Performance Monitoring we discovered that indexes needed to be rebuilt. The performance metrics of the data layer were too slow for the application layer, so the CRM interface was receiving a general timeout error and sending it back to the users in the form of this generic message!

This reminded us of a few very important factors to keep in mind for any CRM [On Premise] deployment.(CRM Online deployments won’t apply since the hosting data center proactively monitors and manages the data layer routinely – win one for the Cloud!)

First, it is important to remember that CRM automatically manages indexes for all system tables and custom entities. It will also create a core Primary Key/Foreign Key relationship between entity relationships; even when the relationships exist only between custom entities. However, in most cases system entities are optimized by containing several additional indexes that get created during the original installation (or Update Rollup application) of the CRM Server. This is what can be missing with custom entities and lend toward problems. Without this added level of initial optimization, custom entities can become corrupted after bulk loading and bulk deletions. Broken or missing indexes leads to slower performance, which in turn can lead to data layer timeouts and the error that we were seeing.

Second, in this day and age of advanced and enhanced Line of Business (LOB) applications, it is safe to assume that large sets of data could be continually loaded and unloaded from a system, thereby reproducing the issue just described. After all, CRM does natively expose 100% of the custom entities added to the out of the box Import Wizard, so for everyday users to bulk load and bulk delete is a very real (and business viable) prospect.

Third and final, taking into account these first two comments, it makes good sense to build out a SQL Maintenance plan that includes a nightly re-organization of all core entity, custom entity, and entity extension tables and a weekly rebuild of indexes for all tables in the organization database (including the metadataschema.[name] tables). It’s important to note that the recommended fragmentation threshold for reorganizing indexes is 30% and greater than that the recommendation is to rebuild the indexes. Identifying a table’s current fragmentation is as easy as using the DBCC SHOWCONTIG procedure or better yet, the new sys.dm_db_index_physical_stats object since dbcc showcontig is deprecated post SQL 2008 R2.) Also, be sure to note which licensing version of SQL is installed. All license versions except Enterprise will require the database to be put into Single-user mode during a rebuild of indexes, so if you plan to perform online indexing be sure you have the right license version first!!