Read Committed Snapshot Isolation and Blocking

  • kevaburg

    SSCoach

    Points: 18130

    Hi everyone,

    We have an application that allows users to add and remove columns in the database in a particular table.  This table comprises around 80% of the total size of the database and when a column is added results in the complete blocking of the table and subsequently the associated database (every transaction run touches this table).  Considering that the database is around 30TB you can imagine how long the blocking lasts.

    My question is this:  Would using Read-Committed Snapshot Isolation prevent this blocking by reading data out of the version store?

    I would be grateful to hear your comments on this and maybe even help to provide a solution.

    Regards,

    Kevin

    • This topic was modified 1 week, 2 days ago by  kevaburg.
  • Jo Pattyn

    SSC-Dedicated

    Points: 31409

    I don't think that Read-Committed Snapshot isolation would help. Would be strange/bulky that sql server to keep the meta data of the table per new insert/update/delete

    Changes to tablestructure takes a lock. It could be minimal if the added column is nullable of with a default value. Some changes from int to bigint are also fast.

  • kevaburg

    SSCoach

    Points: 18130

    Hi Jo,

    Many thanks for the answer.  Had I considered DDL actions and DML I should have come to the same conclusion.

    Ho-hum.....then I need another solution.

    Regards,

    Kevin

  • Grant Fritchey

    SSC Guru

    Points: 396714

    How often are you adding columns to this table? If it's frequent, it sounds like you'd benefit from normalization. Actually, it sounds like you'd benefit from normalization a lot.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • kevaburg

    SSCoach

    Points: 18130

    Unfortunately this application doesn't work quite that simply.  "Relativity" is an eDiscovery platform that allows users in the course of their work to add and remove fields that they believe are or are not relevant.  These fields are all stored in this one table and can be added and removed as required.  The problem is that during the course of adding and removing fields there is a exclusive lock held on the table.

    I thought at first that using RCSI might help reduce the locking by reading data out of the version store while the DDL is running but that doesn't appear to be the case.

    To that end we have had to create windows to enable these fields to be added and removed without disrupting work more than is necessary.

  • Grant Fritchey

    SSC Guru

    Points: 396714

    You're not going to be able to get around the need for a lock to perform DDL. No matter what. Again, it sounds like architecture is more of the problem. Either normalization, or, completely decoupling and going with a document definition instead. You're just not going to get performance out of a 30tb table that has to add & remove columns all day long. It's always going to be blocking, even if those columns are nullable. That will radically reduce the exclusive lock times, but it can't eliminate them.

    Sorry. I wish I could suggest something better, but that's where I'm falling based on the info at hand.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • DesNorton

    SSC-Insane

    Points: 23283

    If the users can add/remove columns to a table at will, I would actually consider this a good candidate for an EAV table.

  • Grant Fritchey

    SSC Guru

    Points: 396714

    DesNorton wrote:

    If the users can add/remove columns to a table at will, I would actually consider this a good candidate for an EAV table.

    This!

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Jason A. Long

    SSC-Insane

    Points: 23711

    EAV table introduce their own sets of problems. In my opinion, a better option would be to create new "1 to 1" tables, rather than add (and remove) columns to a single monolithic table.

    This way, you're never altering the main table, and you don't waste space with columns that are 90% NULL.

    From there, it's easy enough to create a view that joins all of the "user created tables" and displays like the table you currently have.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23167

    As a thought, it may not hurt to reach out to the creators of "Relativity" to see what they recommend.  Often the creators of the tool will have some good ideas on how to best use their tools.

    I expect that they were not thinking anyone would be having a 30 TB table for their tool to run against.

    Not entirely sure on your setup, but is it possible to archive some of that data?  If you reduce the size of the table, you will hold a shorter lock while adding and removing columns.

    At my workplace we had a 1 TB database that was 90% useless data.  Removing the junk (ancient data that nobody looked at) helped performance on that database and improved our backup and maintenance times drastically.

  • Jeff Moden

    SSC Guru

    Points: 997150

    kevaburg wrote:

    Unfortunately this application doesn't work quite that simply.  "Relativity" is an eDiscovery platform that allows users in the course of their work to add and remove fields that they believe are or are not relevant.  These fields are all stored in this one table and can be added and removed as required.  The problem is that during the course of adding and removing fields there is a exclusive lock held on the table.

    I thought at first that using RCSI might help reduce the locking by reading data out of the version store while the DDL is running but that doesn't appear to be the case.

    To that end we have had to create windows to enable these fields to be added and removed without disrupting work more than is necessary.

    Does the software have an "oops" button that will allow the users to somehow recover a "field" (table column) that was accidentally removed because what they thought wasn't relevant suddenly turns out to be relevant?  Or does it just "mark" the column as non-relevant without actually dropping it?

    I agree with Brian... Reach out to the manufacturers of the "Relativity" product for help.

    Shifting gears a bit, if the eDiscovery process you're using threads emails into a Parent-Child hierarchy to help you discover relevant, non-relevant, admissible, and "privileged" legs in an email chain by sampling/reviewing various "near leaf-level" and "near branch junction" documents, I know of some ways to do that faster than ever.

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • kevaburg

    SSCoach

    Points: 18130

    *smile* an "oooops" button!  That made me smile!

    Unfortunately not.  That is why adding fields to the bigger databases causes havoc for those trying to meet deadlines on that workspace.

     

    I am not sure what an "EAV" table is so I will have a look.  Obviously, because this is an established (and trusted) product I don't expect too much in the way of quick fixes but who knows, maybe in a later version....

Viewing 12 posts - 1 through 12 (of 12 total)

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