Share via

Dynamics 365 organization database indexes

  • Article

 

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.

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 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.

See Also

Operating Microsoft Dynamics 365
Microsoft Dynamics 365 data protection and recovery

© 2016 Microsoft. All rights reserved. Copyright