Post reply

Blocking issues

  • September 1, 2009 at 9:27 am

    #133342

    We have a database which is accessed by 24x7. However, we have a table which needs to be updated once a day. We found that the minimal users access happens in about 4:00 am, so our job is scheduled for that time.

    This job selects data into a temp table, and then inserts new records into destination tables and updates records. The destination table is locked for approx 5-10 sec.

    The table size is about 1 mln recs, it inserts about 167 k records in average and updates about 15k records.

    Is there anything we can do to reduce locking time or completely eliminate it ? Are there any techniques, tricks to do this ?

    Thanks.

  • September 2, 2009 at 11:07 am

    #1048300

    Doesn't selecting into a temp table (SELECT * INTO #a FROM whatever) lock tempdb for the duration of the operation?

  • September 2, 2009 at 1:26 pm

    #1048401

    It locks just #a, not entire tempdb

  • September 2, 2009 at 2:01 pm

    #1048425

    dsdeming (9/2/2009)

    Doesn't selecting into a temp table (SELECT * INTO #a FROM whatever) lock tempdb for the duration of the operation?

    That used to be true, somewhere around version 6.5.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 2, 2009 at 2:03 pm

    #1048426

    SQL Guy (9/1/2009)

    Is there anything we can do to reduce locking time or completely eliminate it ? Are there any techniques, tricks to do this ?

    Maybe.

    Can you read through this article to see how best to post performance-related questions - http://qa.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 3, 2009 at 7:26 am

    #1048716

    I recommend batching up the inserts and updates so you only do some subset of the records in each transaction. This is very simple to do if you have identities as you PK. Otherwise you can put the relevant keys into a driver table with an identity on it and use that.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply