SQL Server 2008 - Query takes forever to finish even though work is actually done

Question

Running the following simple query in SSMS:

UPDATE tblEntityAddress SET strPostCode= REPLACE(strPostCode,' ','')

The update to the data (at least in memory) is complete in under a minute. I verified this by performing another query with transaction isolation level read uncommitted. The update query, however, continues to run for another 30 minutes. What is the issue here? Is this caused by a delay to write to disk?

TIA

Answer 1

Most probably, you transaction is locked by another transaction which affected tblEntityAddress.

Run sp_lock and see if tblEntityAddress is locked by another process.

Answer 2

In a separate SSMS window, try running the following:

SELECT status, wait_type
FROM sys.dm_exec_requests
WHERE session_id = <SPID>

Just replaced with the SPID associated with your UPDATE query (number in brackets after your login name in the bottom bar).

Run the above a few times in succession and note what the wait_type is. There are numerous types of waits - see what that is (& let use know), it could highlight the cause.

Update: Quotes from this MS KB article:

IO_COMPLETION

This waittype indicates that the SPID is waiting for the I/O requests to complete. When you notice this waittype for an SPID in the sysprocesses system table, you must identify the disk bottlenecks by using the performance monitor counters, profiler trace, the fn_virtualfilestats system table-valued function, and the SHOWPLAN option to analyze the query plans that correspond to the SPID. You can reduce this waittype by adding additional I/O bandwidth or balancing I/O across other drives. You can also reduce I/O by using indexing, look for bad query plans, and look for memory pressure.

Answer 3

Another thing to consider is a slow running trigger.