September 15, 2020 at 1:01 pm
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
September 15, 2020 at 1:48 pm
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.
September 15, 2020 at 2:44 pm
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
September 16, 2020 at 11:31 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2020 at 12:10 pm
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.
September 16, 2020 at 12:42 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2020 at 12:51 pm
If the users can add/remove columns to a table at will, I would actually consider this a good candidate for an EAV table.
September 16, 2020 at 2:24 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2020 at 2:37 pm
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.
September 16, 2020 at 4:54 pm
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 17, 2020 at 3:53 am
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
Change is inevitable... Change for the better is not.
September 17, 2020 at 12:40 pm
*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....
October 7, 2020 at 2:00 pm
Are you sure your data system really needs it all in actual columns? ( advocate of the devil, I know )
Have you considered putting those "free wheeling columns" into an XML document ?
just another option to look into, when all others seem to fail
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 7, 2020 at 3:54 pm
Are you sure your data system really needs it all in actual columns? ( advocate of the devil, I know )
Have you considered putting those "free wheeling columns" into an XML document ?
just another option to look into, when all others seem to fail
I have to admit, there's virtually no way that I'd use an XML document for this because, if data exists, they'll want to do a "search" for it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply