So I found tons of unused indexes from our CRM application with this query from Pinal:

o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
+ ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

I was reading about the CRM application and how it creates it's own indexes and etc.

I don't know if this is the right place to ask a question about Microsoft CRM but, is it safe to drop those indexes with 0 seeks, lookups and scans with tons of updates and rows?

I can find problems it they hard coded that index inside the source code, but, is this a good pratice that microsoft uses?

Note: I can close this post if this is not the place to ask this question.

  • 1
    Whatever answers you get, I would suggest that you don’t remove any indexes. Microsoft created them for a reason. There will be a built-in report or something that needs it, and hasn’t been used yet
    – Philᵀᴹ
    Commented Jul 24, 2019 at 21:33

2 Answers 2


I don't know if this is the right place to ask a question about Microsoft CRM but, is it safe to drop those indexes with 0 seeks, lookups and scans with tons of updates and rows?

That will depend on multiple factors only you will know. (Both SQL Server & Microsoft Dynamics CRM related)

SQL Server

  • When was the server last restarted?

Make sure this index usage data has enough history to go on as it will reset after each instance restart.

Also, since you are using sql server 2012, the data could have been reset after an index rebuild if your version is below SP2+CU12 or SP3+CU3. (Which hopefully is not the case)

More on that in this blogpost by Kendra Little

If either the data is reset or the restart was recent, consider logging the information to a table and reporting on these results instead.

You could opt to do this regardless.

  • Are there rarely used queries that need these indexes?

This is harder to track and is also more unlikely, but could still happen.

For more concerns in general to dropping indexes, read this and this or many more (online) sources.

Microsoft Dynamics CRM

  • Is it an out of the box or user created index?

According to this post there are indexes you can and can't remove.

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

This simply means that you cannot drop the indexes that are included by MS Dynamics CRM (Out of the box).

You can drop your own / user created indexes after veryfing these are, in fact, user created indexes.

As always, document any changes you make to these user created indexes and monitor the impact.


I've actually dropped CRM indexes and have found the problem it creates. By default, CRM has two index maintenance jobs that run daily. On calls the stored procedure p_Reindex and the other calls p_ReindexAll.

p_Reindex has a cursor for the indexes listed in the CRM_MSCRM.MetadataSchema.EntityIndex table and runs DBCC DBREINDEX commands for each row in the table. If it processes a non-existent index, the command will fail and the job will stop.

MY OPINION is that if you've got proper index maintenance jobs (ala Ola Hallengren), then the failure of the job that calls p_Reindex is not a problem.

I've actually run into problems with the CRM maintenance jobs conflicting with the Ola Hallengren jobs so I decided to stop the CRM jobs from making any changes. I don't have access to the CRM application to disable the jobs (nor the knowledge on how to do so), instead I've effectively stopped them from making database changes by updating the code within p_Reindex and p_ReindexAll to include AND 1=2 in the WHERE clause of the query that generates the list of indexes to process. The query then returns zero rows and the job completes without altering any indexes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.