Sample Header Ad - 728x90

Delete unused indexes from Microsoft Dynamics CRM Application

4 votes
2 answers
751 views
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.
Asked by Racer SQL (7546 rep)
Jul 24, 2019, 08:07 PM
Last activity: Oct 16, 2023, 09:46 PM