Sql-server – How to disable or delete an index

indexlockingsql serversql-server-2008-r2

I created a non-clustered index on a live server (SQL Server 2008 R2) and it turned out not to give the performance benefits that I was hoping for. So I want to delete the index again.

Unfortunately I cannot get a lock on the index as it is in use. It's probably always going to be in use. Restarting SQL Server is not really an option.

I've also had this same problem in the past when I've wanted to rebuild an index, and I've had to work around the issue by reorganising instead (which is sometimes very inefficient).

Is there any way to tell SQL Server to stop using an index? Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index? Any other options? Or do I just have to wait until there's some kind of outage and then hope I remember to remove it before everything comes back online?

Best Answer

Is there any way to tell SQL to stop using an index?

That would be disabling an index:

alter index IX_YourIndex
on dbo.YourTable
disable;
go

But that, too, will be running into concurrency issues on a highly transactional object, as that operation above still requires a schema-modify (Sch-M) lock on the object.

Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index?

That would be the default behavior of SQL Server. The only "command timeout" is going to be enforced by client-side software. I'm going to assume that you're using some sort of GUI to try to delete/disable the index (SSMS maybe?). Either way, if you were to put this in a new query window (SSMS), it would wait indefinitely for the locked resource to become available so it can be granted the necessary locking:

drop index IX_YourIndex
on dbo.YourTable;
go

This is one of the reasons why it's almost always easiest to do these types of operations during a slow time, or a maintenance window. That typically can ensure little to no user activity, making these simple tasks...simple and quick.

And if you want to see what/who your drop index request is waiting on, you can run a query similar to this:

select
    session_id,
    command,
    wait_type,
    wait_time,
    blocking_session_id,
    wait_resource
from sys.dm_exec_requests
where command = 'drop index';
Related Question