Dynamics 365 organization database indexes

Dynamics CRM 2016
 

Updated: December 9, 2016

Applies To: Dynamics 365 (on-premises), Dynamics CRM 2016

The indexes that are created in a Microsoft Dynamics 365 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 365 organization database.

  • Depending on the version and update applied, a Microsoft Dynamics 365 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 365 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.

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

For more information about SQL Server indexes, see SQL Server Index Design Guide.

© 2016 Microsoft. All rights reserved. Copyright

Community Additions

ADD
Show: