Dynamics CRM organization database indexes

Dynamics CRM 2016

 

https://technet.microsoft.com/en-us/library/mt679036.aspx

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 Microsoft 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 Microsoft 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 these links to download Microsoft Office Excel worksheets that contain lists of indexes.