Have Geek Brain

SQL Server 2008 - Query Ends Permanently Even When Job Done

Executing the following simple query in SSMS:

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

Data update (at least in memory) completed in a minute. I verified this by running another query with a transaction isolation level read uncommitted. However, the update request continues to run for another 30 minutes. What is the problem? Is this due to latency for writing to disk?

TIA

+2


a source to share


3 answers


Most likely your transaction was blocked by another transaction that affected tblEntityAddress

.



Run sp_lock

and see if blocked by tblEntityAddress

another process.

+2


a source


In a separate SSMS window, try running the following:

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

Just replace the SPID associated with the UPDATE query (the number in brackets after your login on the bottom line).

Do the above several times in a row and notice what the wait_type is. There are many types of expectations - see what this means (and let's use them), it can highlight the reason.



Update: Quoted from this MS KB article :

IO_COMPLETION

This wait type indicates that the SPID is waiting for full I / O requests. When you notice this waittype for a SPID in the sysprocesses system table, you must determine disk bottlenecks using performance monitor counters, profiler trace, table function fn_virtualfilestats system, and SHOWPLAN option to parse query plans that match SPIDs. You can reduce this type of latency by adding additional I / O bandwidth or balancing I / O on other disks. You can also reduce I / O by using indexing, looking for bad query plans and memory lookup pressure.

+2


a source


Another thing to consider is the slow trigger.

0


a source






More articles:


All Articles