SQL Server Performance

Bulk Inserts and acquiring locks

Discussion in 'Performance Tuning for DBAs' started by ggeller, Feb 24, 2010.

  1. ggeller New Member

    We are using sql server 2000 standard edition on windows server 2000. We have several DTSs that move millions of rows of data. In the past couple days we have been getting the following errors:
    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.)
    Last night I ran sp_lock while this was happening and one spid had over 10 million locks. I believe most were rowlocks, RID. The operation according to sp_who2 is a BULK INSERT which I believe is from a SELECT...INTO. I guess I haven't narrowed that down yet. I'm still trying to determine which job was causing all this.
    Right now I'm monitoring a job that is doing a BULK INSERT and the majority of the locks are EXT exclusive. Right now it is in the thousands, which seems to be normal. There is also an TAB exclusive mixed in.
    I'm still a bit new to analyzing performance, but it is normal for a BULK INSERT to just keep acquiring locks until the operation is done? Also I thought maybe specifying a table lock would help, but it appears there is already a table lock.
    I'm not sure why the BULK INSERT would use row locks but that appears to be the problem, just too many for our setup. If anyone could shed a little light on what I'm seeing and if a TABLOCKX is a possible solution. I'm hoping to get the offending job identified soon and I can post specifics.
  2. satya Moderator

    Welcome to the forums.
    Is there a way to control the execution of those DTS Packages in serial order, if they are multiple DTS package executions at same time?
    Refer to the MSDN http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp on TABLOCK

    Specifies that a table-level lock is acquired for the duration of the bulk copy operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance.

    By experience I wouldn't recommend that on active table that might cause further performance issues. Also as per the BOL:
    For a large bulk copy operation, the entire operation is treated as a single transaction. When you use the batch parameter (-b), the bcp utility will treat the operation in small transactions with the number of rows specified. At the end of each small transaction, the system resources held by that transaction are freed, so fewer locks are needed.
  3. ggeller New Member

    Thanks for the response. I have some more information. The execution is done in serial. The DTS uses a data transform task with a query as the source and a table for the destination. So that is where the BULK INSERT comes from. The table shouldn't be being used so I believe a table lock will be fine.

    But here is some more information. We run two big sequences of DTSs twice daily. The lock errors had only been happening during the morning runs. So yesterday we monitored both the afternoon runs and everything looked fine. So it was looking like a morning issue. The only difference I could see was another DTS was accessing the same table the BULK INSERT was inserting into. They both started at the same time. I moved the separate DTS to a few hours later. So this mornings DTSs ran fine. At this point I don't know if the separate DTS was the problem so I guess that's what I want to get an opinion on.
    Here is my guess. The jobs had been running fine for months until the last few days. It maybe possible the separate DTS had been causing the BULK INSERT to acquire row locks for awhile, but it finally became too much for our system. So by moving the separate DTS to a later time the BULK INSERT now only acquires EXT and PAG locks. Keep in mind that BULK INSERT is the command I see when doing a sp_who2. Again the DTS uses a data transform task and the is what is initiating the BULK INSERT. Does my explanation make sense?

Share This Page