January 22, 2020 at 3:27 pm
New DB will be created. Will store DocID, DocType,JSON, Path and datetime in a master table with indexing in separate tables for each document type.
Deadlocks are a concern, since we will be adding documents at a time and selecting the data for reporting purposed.
So i came up with few options:
Create table in memory(in memory) - It is a new db so i don't have the size of the db, so my understanding memory requirement should be more than the db size right?
So in memory and table partitioning can be implemented together for same table?
How about replication so the inserts are done on OLTP and reporting is done from reporting server.
Please advise? Thanks in advance!
January 22, 2020 at 4:05 pm
Any thoughts?
January 22, 2020 at 4:57 pm
Here's a link to some documentation about in-memory tables and the feature limitations:
It looks like in-memory and partitioning do not play well together, and there are a number of restrictions on replication with a deeper link inside that article. If the only problem you're trying to solve is deadlocks, maybe look at just changing the isolation level of how you access the data?
January 22, 2020 at 5:39 pm
how have you determined that you need in memory tables? can you supply the requirements matrix that you used for it?
unless you are looking into thousands of transactions per second memory tables is most likely over the top for your needs and does not solve the issues that you have with deadlocks - that is pure code related.
January 22, 2020 at 8:20 pm
We don't have deadlocks yet. But this is a new design so i am trying to come up with a solution so that it will not fall as victim of deadlocks. Lot of inserts and then selects would happen.
January 22, 2020 at 8:36 pm
Without knowing much other than the data insert and select. How much memory to start with on the server. Is there any ballpark?
January 22, 2020 at 8:49 pm
it all depends on the requirements matrix that you should have done already - that will give you a hint of what you may need.
its not enough to say that data insert and select will happen - that is a taken -
January 22, 2020 at 9:50 pm
Regarding comment from chris, it says replication limitation for sql 2016..But it doesn't say about SQL Server 2017.
Here's a link to some documentation about in-memory tables and the feature limitations:
It looks like in-memory and partitioning do not play well together, and there are a number of restrictions on replication with a deeper link inside that article.
January 23, 2020 at 1:59 pm
The way Microsoft documentation online works, there is a dropdown in the left pane of the webpage that lets you select the supported versions. The pages for 2016, 2017, 2019 look similar in these limitations:
Have you considered the other thing that Frederico and myself have been saying? Depending on your expected transaction load and other environment factors, in-memory tables is a complicated setup that will likely be overkill for your company's needs. Something like using snapshot isolation level could solve your problem of too much blocking and deadlocks.
January 23, 2020 at 4:03 pm
Perhaps you can start with readcommited snapshot isolation instead of in-memory? The optimistic locking should reduce blocking from reporting
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply