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