The indexes that are created in a Dynamics 365 Customer Engagement (on-premises) 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 Dynamics 365 Customer Engagement (on-premises) organization database.
Depending on the version and update applied, a Dynamics 365 Customer Engagement (on-premises) 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 365 Customer Engagement (on-premises) 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 Dynamics 365 Customer Engagement (on-premises), select from these links to download Office Excel worksheets that contain lists of indexes.
Microsoft Download Center: List of Dynamics CRM 2016 RTM out-of-box indexes
Microsoft Download Center: List of Dynamics CRM 2015 Update 0.2 out-of-box indexes
Microsoft Download Center: List of Dynamics CRM 2013 Service Pack 1 out-of-box indexes
Microsoft Download Center: List of Dynamics CRM 2011 Update Rollup 18 out-of-box indexes
For more information about SQL Server indexes, see [SQL Server Index Architecture and Design Guide]/sql/relational-databases/sql-server-index-design-guide?view=sql-server-2016).
See Also
Operating Microsoft Dynamics 365 Customer Engagement (on-premises)
Microsoft Dynamics 365 Customer Engagement (on-premises) data protection and recovery