Sql-server – Delete unused indexes from Microsoft Dynamics CRM Application

index-tuningmicrosoft-dynamicssql serversql-server-2012

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

SELECT 
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
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' 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
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
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
GO

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.

Best Answer

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.

Related Question