Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210

    Unanswered: Going crazy with inconsistant batch duration

    First; thanks for looking.

    On SQL Server 2000. IBM X series, dual 2GHz Xeon processors, 3GB RAM, RAID 5 DB array and separate Mirrored drive for OS, Windows 2000 Server.

    Note: I should have moved the log files to the mirrored volume - I only just noticed it wasn't like that ... oops. It only has the OS and backup files (different logical volumes).

    Problem: I have a Bill of Material system. It has a batch (Stored Procedure) that explodes Order Line Items. Each line has about 40 components.

    The SP has 5 major steps to explode the Assembly, figure out colors/sizes to get the actual part numbers, assign quantities, update flags on the line item table (that has 3,500,000 rows but is not a clustered index - fill factor 85% on the unique autonumber index I use for the updates). The SP is jam packed with User Function calls, sub-Stored Procedures, and (yes) some cursors (on small declared temp tables created for each Order Line processed).

    The execution plan for the whole batch of 100 is humongous - like 1000 pages, however nearly all steps are about 16 - 40 milliseconds to execute.

    It normally takes 13 seconds to process 100 line items (3900 assembly rows created). However; about every 20th run, it takes 2 or 3 minutes ON THE VERY SAME LINE ITEMS. No programming changes! Run back-to-back, or with a minute or two delay. Then running it again, back to 13 seconds. Sometimes it takes 25 or so seconds, but that oddball 3 minutes is a killer.

    I put some timing INSERTs into a temporary log file to localize the problem.

    So; here's the REALLY weird thing. The part that experiences the intermittent 2 minute delay is a VERY SIMPLE call to a SP that INSERTs to the tbBATCHs table and gets the identity and passes it back with an Output variable. The tbBATCHs table only has about 8 columns. Nobody else uses it so it's not locking. It has an "If Exists" selection following the Insert to see if another batch is in progress not yet flagged as complete, but's not the issue because it's never true (hard coded values for testing forces this).

    I have a Standby Server (duplicate config of main server) so I ran it there. Same issue.

    If I loop 100 times on the tbBATCHs INSERT SP, it is pretty quick (like 2 seconds for all 100).

    It's never on the first run, so I think it may be some sort of Log File catch-up.

    Also; I DO NOT have Transaction Logging in place yet.

    Ideas?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In addition to the timestamps, also save the waittype, waitresource, and yes the blocked columns to the log table. This will give you more of an idea of what the process is doing at various times.


    EDIT: Sorry. Add these values from the master..sysprocesses table, where spid = @@spid

  3. #3
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by MCrowley
    In addition to the timestamps, also save the waittype, waitresource, and yes the blocked columns to the log table. This will give you more of an idea of what the process is doing at various times.


    EDIT: Sorry. Add these values from the master..sysprocesses table, where spid = @@spid
    Thanks for the clue. Based on this microsoft article, I'll also capture lastwaittime.

    I'll know later today what it captures. I put a 10 second WAITFOR DELAY between jobs and in 100 cycles it didn't recur. That's no guarantee since I'd really like to know what was happening.

    Side note: I put Transaction Logging (BEGIN TRANSACTION / COMMIT TRANSACTION) around the process. It took like 1/2 the time, but it holds some serious (no read) locks on the tables updated, very scary yet worthy of working around.

    Our system was previously very simple, without need for many Transactions. Only 2 or 3 nightly processes have them. This process will run throughout the day. COMMIT was a way of life in Oracle but I didn't recall it causing no-read locking problems. I wonder if SQL 2005 has this same approach.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Oracle and SQL Server have different ways of handling locks, and transactions.
    In Oracle, an update process does not block read processes, but the read processes get a before image of the data. In SQL Server, update processes block read processes, but all processes are guaranteed an after-image of the data.

    In Oracle, you must explicitly commit all transactions. In SQL Server, any transaction is implicitly committed, unless you explicitly begin a transaction (with BEGIN TRANSACTION). Normally, you do not need to pepper your SQL Server script with "commit"s

  5. #5
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Quote Originally Posted by MCrowley
    Oracle and SQL Server have different ways of handling locks, and transactions.
    In Oracle, an update process does not block read processes, but the read processes get a before image of the data. In SQL Server, update processes block read processes, but all processes are guaranteed an after-image of the data.

    In Oracle, you must explicitly commit all transactions. In SQL Server, any transaction is implicitly committed, unless you explicitly begin a transaction (with BEGIN TRANSACTION). Normally, you do not need to pepper your SQL Server script with "commit"s
    Is it a table lock or just at the row level?

    I'm tempted to put all the updated Order Line Item values into a linked table. It would be unacceptable to cause Report failures because some flag I only use on this update process (ie: unrelated to Sales Report needs). Still; Material Cost is updated on the Order Line Item - and that report could be wanted at any time (even at the screen level, when trying to determine discounted pricing). I'm beginning to appreciate Oracle's cursor oriented methodology more and more.

    Now I'm wondering, is it Table locks or Row locks? Also, what tables get locked; only the ones subject to an Update or Insert, I'm hoping so but I'd hate to be wrong?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by vich
    Is it a table lock or just at the row level?
    Depends on what the index statistics and query plan determine is best. Searching a phone book for any last name like '%son' is a table lock. Searching for last name = 'vich' is generally a row lock.

Posting Permissions

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