Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2004
    Posts
    28

    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?

    Your thoughts would be greatly appreciated.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    1) Select statements put shared locks on tables. These allow other readers, but block writers to the locked portions of the tables. Shared locks are blocked by existing Exclusive locks.

    2) If you like partial results, using (nolock) is perfectly fine. I wouldn't suggest it to an accountant, though.

    3) Could be, but you would need to monitor the process as it is running to be certain.

  3. #3
    Join Date
    Oct 2004
    Posts
    28
    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.

  4. #4
    Join Date
    Oct 2004
    Posts
    28
    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.


    Next steps????
    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)?

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  6. #6
    Join Date
    Oct 2004
    Posts
    28
    Never used sysprocesses table before. Looking at it for first time I notice a few suspect entries. Is this just a current snapshot or does it keep archive info. It only had 3 pertinent rows out of 22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •