Post reply

Table with only one record

  • November 9, 2009 at 4:19 am

    #1076579

    dant12 (11/8/2009)

    just shooting out options here, the only one who knows his entire environment is himself so the decision is up to him, just wanted to expose more options

    Absolutely. Me too.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 9, 2009 at 3:39 pm

    #1076993

    dant12 (11/8/2009)

    if your using SQL 2008 you can disable lock escalation on the problematic tables and enforce rowlocking on the table, that should solve the concurrency problem

    if your using 2005 you could try implementing some sort of loop to run the updates in smaller batches in an effort to avoid table locks

    Dan: Yes, this is 2005. I already tried to implement small batches for updates, they reduced number of blockings, but did not completely eliminate them.

    I also tried sp_rename method. While application is accessing working table, I load another one in the background, and when it finishes I rename it with sp_rename. But unfortunately our DBA's prohibit using sp_rename in production environment.

  • November 12, 2009 at 8:54 pm

    #1078685

    SQL Guy-482125 (11/9/2009)

    But unfortunately our DBA's prohibit using sp_rename in production environment.

    Then use a synonym and alter it to where it's pointing.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 16 through 17 (of 17 total)

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