June 28, 2010 at 1:46 pm
System: SQL Server 2008 SP1 Standard Edition on dual Xeon server. 12G RAM, about 750 GBytes free space on disk.
I have an SSIS application which includes SQL procedures, run in an Execute SQL Task, to process the data after it's been loaded. Normally it does the complete data load and process in about 10 minutes, but today it sat in one SQL task making no obvious progress, until I killed it 2 hours later. When I restarted SQL Server and ran the whole SSIS data load again, it worked fine.
This procedure is a series of SQL UPDATE queries. Logging showed that the first few had run normally. The step that never finished was
UPDATE [dbo].[ORDER]
SET [Household_Id] = c.[Household_Id]
FROM [dbo].[ORDER] o LEFT JOIN dbo.CUSTOMER c ON o.[Customer_Number] = c.[Customer_Number]
WHERE o.[Client_Id] = @ClientId;
Normally this only takes a few seconds to run.
I did some tests while the task was running. Nobody else was accessing the database at the time, 3 GB of the 12 GB RAM was free, CPU load for SQL Server was high in one core but the others were idle, and disk traffic to the database files was very low - a few KBytes/minute. Indices should be OK; they had been defragmented this morning. Backup had finished about an hour ago so it wasn't that.
What I DID find was that the [dbo].[ORDER] table was blocked. If I tried to run another query on it in SQL Server Management Studio - even a simple SELECT * FROM [dbo].[ORDER]; - it just sat there clocking up minutes - no output. The same query on any other table including [dbo].[CUSTOMER] started to return rows immediately.
I've had this happen twice before, a few weeks ago. The first time, I was able to leave it running over a weekend - and it still hadn't finished 3 days later! (That was an earlier version of the system so may not have been the same code.)
Does anyone have any idea why my task SOMETIMES locks up, or some tests that might show what is happening?
June 28, 2010 at 2:12 pm
Check what the blocking query is. If you ckeck sys.dm_exec_requests, you'll see a columns called blocking_session_id. That's the session_id of the session that's holding the locks that prevent the query from running.
Query sys.dm_exec_requests, sys.dm_exec_sessions and sys.dm_exec_sql_text and you can get a whole lot of info about that session, including what it's running and where it's connected from.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2010 at 3:51 pm
Thanks! I'll put them into a script so I've got them ready next time it happens.
(I just tried it and got quite a list, but they all had blocking_session_id = 0, which is what I'd expect now nothing's blocking.)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply