Dynamics CRM indexes (missing, redundant, and OOB)


It is a fact that Dynamics CRM is an OLTP application, and that it’s performance relies heavily on how well the back end database (SQL server) is performing.  A major component in the performance matrix is indexes.  They have to be well defragmented, you should have the needed indexes to optimize query performance (missing indexes), and you should not have idle indexes that have more writes than reads (redundant indexes).

Adding missing indexes

The Missing Indexes report/query has very favourable effect on performance although it has limitations. (Limitations of the Missing Indexes Feature) But it recommends very effective indexes based on the actual usage and queries that SQL server receives from your end users,

Removing redundant indexes (editing / removing the OOB indexes is not supported!)

Be careful not to create too many indexes, which can affect the insert and update performance. Balance indexing needs according to business requirements, indexes that have a lot of writes and no reads (or very few) are not efficient and are considered a burden to the system (redundant indexes).  So it is a good idea when working with missing indexes to give it some time (depending on your operations cycle maybe a month) and off set them against a redundant indexes query.  However, please remember removing or editing OOB indexes is NOT supported.

Ref: Optimizing and Maintaining Database Indexes

OOB indexes

The famous question is, how do I know the OOB indexes from newly added indexes, The MS content team has recently added new content to shed more light on OOB indexes, but I’d say your best guard is to fully document your indexes and make sure you have proper naming conventions.

The indexes that are created in a Microsoft Dynamics CRM organization database are designed to provide fast retrieval of commonly requested data from tables and views stored on a SQL Server. Here are a few characteristics for the indexes that are created in a Microsoft Dynamics CRM organization database.

  • Depending on the version and update applied, a Microsoft Dynamics CRM organization database without any customizations or installed solutions (out-of-box database) has between 1,000 and 1,600 total indexes.
  • Later versions of Dynamics CRM have more features and, subsequently, more database objects such as tables and indexes.
  • At least five new indexes are created whenever you create a new entity or reference a new column in a quick find.
  • Installing a solution increases the number of total indexes.

How to get a list of all indexes stored in an organization database?

To get a list of all indexes, run the following sample SQL query against the organization database.

SELECT s.name +'.'+t.name AS 'table_name',i.name,i.index_id

FROM sys.schemas s JOIN sys.tables t ON s.schema_id=t.schema_id

JOIN sys.indexes i ON t.object_id=i.object_id LEFT OUTER JOIN sys.objects o

ON o.parent_object_id=t.object_id AND i.name=o.name

WHERE i.name is not null

 

For an approximation of the out-of-box indexes in an organization database that corresponds to a specific version of Microsoft Dynamics CRM, select from the links below to an Excel worksheet that contains a list of indexes.

For more information about SQL Server indexes, see SQL Server Index Design Guide, Microsoft Dynamics CRM Server installed configuration components

Comments (2)

  1. Ian Smith says:

    We have a heavily-customized Opportunity form with lots of lookups to different types of “contractors” — one for each “trade” — so, there’s about 25 or 30 of them. This is CRM Online. We noticed that the form loads slowly at times; with the test environment being even more slow. How do we go about determining if we need to add indexes to CRM Online databases to improve performance?

    1. Do you have a Premier Support agreement? If so, you can open a support case to provide this information. I think that you can do it with a normal support case as well. We now have alternate key support in CRMOL, so if you have a candidate for this, you can create an alternate key (https://blogs.msdn.microsoft.com/crminthefield/2016/02/01/dynamics-crm-indexes-missing-redundant-and-oob). The alternate key also adds an index. Outside of these two options, there is currently not a way for you to manage custom indexes for CRMOL.

Skip to main content