CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2non-clustered-index
Andy M
Andy M

Reputation: 6065

What happens when a nonclustered index is deleted?

What happens on the SQL Server engine side when I'm deleting an index from one of my tables?

Details: I have a database running into production.

In this database, I have a query that creates deadlocks on a regular basis. I've found the query creating the deadlock, ran it on my computer, showing its execution plan. SQL Server Management Studio proposes to add an index on one specific table.

The index makes sense to me but my problem is that, on this table I already have 3 indexes and, to be honest, I cannot be sure if they're properly used or if they've been created for a specific role.

I could simply add one more index on the table but I'm concerned about the cost I'll pay each time I add/update/delete data on the table.

I made a few attempts on my machine and it seems that I need to delete at least two other indexes to make the engine select the index I'm creating today (looks odd to me). As soon as I force the engine to take my index (because I deleted everything else), the query runs 10 times faster.

Can I simply use the DROP Index command without much problem? I don't have to rebuild or anything?

Upvotes: 2

Views: 3000

Answers (2)

Igor Micev
Igor Micev

Reputation: 1662

You can run this query to get know whether the indexes of a table are used or not

SELECT TOP 50
  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 o.name='your_table_name'
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

Then go with the DROP index statement if any of them are not used. Before dropping, ensure the server has been UP for a long time enough so the indexes got the chance to get used.

Also, having three indexes on a table is not many. Just check they are useful to your queries.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

A non-clustered index is a secondary data structure - it's not "integrated" into the main data structure of the clustered index or heap.

As such, deleting one should be a relatively fast and pain-free experience, since all the system should need to do is remove metadata about the index and mark the index's pages as unallocated.

There shouldn't be a need to "rebuild" or do anything else.

Upvotes: 6

Related Questions