Delete Query for one record going super slow

Question

I'm trying to delete one single record from the database.

Code is very simple:

    SELECT * FROM database.tablename
    WHERE SerialNbr = x

This gives me the one record I'm looking for. It has that SerialNbr plus a number ids that are foreign keys to other tables. I took care of all foreign key constraints to where the next line of the code will start running.

After that the code is followed by:

    DELETE FROM tablename
    WHERE SerialNbr = x

This should be a relatively simple and quick query I would think. However it has now run for 30 minutes with no results. It isn't yelling about any problems with foreign keys or anything like that, it just is taking a very very long time to process. Is there anything I can do to speed up this process? Or am I just stuck waiting? It seems something is wrong that deleting one single record would take this long.

I am using Microsoft SQL Server 2008.

Answer 1

It's not taking a long time to delete the row, it's waiting in line for its turn to access the table. This is called blocking, and is a fundamental part of how databases work. Essentially, you can't delete that row if someone else has a lock on it - they may be reading it and want to be sure it doesn't change (or disappear) before they're done, or they may be trying to update it (to an unsatisfactory end, of course, if you wait it out, since once they commit your delete will remove it anyway).

Check the SPID for the window where you're running the query. If you have to, stop the current instance of the query, then run this:

SELECT @@SPID;

Make note of that number, then try to run the DELETE again. While it's sitting there taking forever, check for a blocker in a different query window:

SELECT blocking_session_id FROM sys.dm_exec_requests WHERE session_id = <that spid>;

Take the number there, and issue something like:

DBCC INPUTBUFFER(<the blocking session id>);

This should give you some idea about what the blocker is doing (you can get other information from sys.dm_exec_sessions etc). From there you can decide what you want to do about it - issue KILL <the spid>;, wait it out, go ask the person what they're doing, etc.

You may need to repeat this process multiple times, e.g. sometimes a blocking chain can be several sessions deep.