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