Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.