Unanswered: Quick SQL Server 2000 Locking question
We are running a 3rd party ETL tool to populate a denormailized version of a production database for reports. Everything works fine 95% of the time. However there is a semi-rare occurence of the ETL tool hanging up. The norm is for the tool to take about 5 times longer than usual, but it still works. Over the weekend however it through an error saying:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
The reports are run through Crystal using stored procs and are all basically select statements
So my question(s) are the following:
1. What kind of lock would a report put on a table (select statement)
2. Would it make sense to change stored procs to use WITH NOLOCK?
3. Or is something else going on?
Here is response I received from 3rd party ETL tool:
Tables/rows seem to be locked by another application or process. We have seen users do this with DBArtisan or Query Analyzer. They lock the table and can't figure out why DT/Engine appears to hang for hours. When they close whichever application that is locking the tables, DT/Engine finishes up what it was doing and everything goes back to normal. You may have a DBA tool like Query Analyzer open looking at either the source or target data as it moves which would result in a hang as the engine waits for the release.
The ETL tool does update some Flag fields just to keep track of what's being moved around. My concern is that the reports run lighting fast with none taking more than 1 second.
The slow down (when it occurs) happens on the same task over and over. You could call it a main enitity table, but its not the largest table. However, most of the reports use it. But we are talking about a 3 hour hang-up on the ETL side. There's no way it wouldn't be able to obtain a lock in that time, right?
Just having query analyzer open is not causing this.
1. Keep a trace going with duration set to more than 10 minutes?
2. Try to track down who's doing what on this server (located across the globe)?
First step is to prove who is blocking and whether there is blocking at all. A three hour block sounds suspiciously like a maintenance job. A three hour slowdown could be SQL Server got starved for memory by some currently unknown process.
Personally, I would spend a night with this thing, to see if I can spot the reason for the slowdown/stoppage. OK, first thing I would do is write scripts to capture the interesting information (the sysprocesses table is a nice place to start. Pay attention to the blocked, waittype, waitresource, and open_tran columns). But then, I am lazy like that.