May 24, 2020 at 11:34 pm
I’ve got a strange situation where I can see the Version Store being used, but I can’t see why. All my reading shows that to use the version store one of the following must be set:
This means essentially all queries that do a Begin Tran (even implicitly) will result in a base transaction being set, and all Create, Update, Deletes after that will be tracked in the VS.
This means connections that set certain options (eg SET TRANSACTION ISOLATION LEVEL SNAPSHOT and SET TRANSACTION ISOLATION LEVEL READ COMMITTED) will result in version store use.
Well on the the relevant user databases, neither RCSI nor Allow Snapshot are set however I’m seeing a lot of rows in the Version Store, and it sometimes grows to 200MB.
The only DBs with Snapshot Enabled in any way are Master, Model & a DBA management database, all three have Allow_Snapshot, and the DBA one also has RCSI.
Another thing to be aware of is the app uses SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in almost all stored procs, but the tests I’ve done show these don’t result in Version Store tracking.
Any help would be appreciated, What have a missed that causes SQL to track page versions in the Version Store?
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 25, 2020 at 6:39 am
the old RTFM does come into place here - just looks like most don't like doing it nowadays.
and Always-On also uses version store.
On a different note - your company must not need accurate data if all your procs have "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" in them - hopefully you are not dealing with critical data
May 25, 2020 at 10:11 pm
Thanks for the reference.
Please don't EVER tell me to RTFM, especially given that I mentioned I'd done a bunch of reading. I've been searching for a reference like this for a while, and all you needed to do was point me to it, the insults were not needed. Despite over 20 years working on SQL Server I know I don't know everything, and I come here to hear from wiser heads than mine, not to be insulted.
Oh, it's not my company, I've been brought in to consult on some other problems, and the use of the Version Store cropped up as part of my investigation.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
May 26, 2020 at 12:05 am
Thanks for the reference.
Oh, it's not my company, I've been brought in to consult on some other problems, and the use of the Version Store cropped up as part of my investigation.
good opportunity to advise them of the bad things that using NOLOCK gives them.
May 26, 2020 at 3:56 am
Leo.Miller wrote:Thanks for the reference.
Oh, it's not my company, I've been brought in to consult on some other problems, and the use of the Version Store cropped up as part of my investigation.
good opportunity to advise them of the bad things that using NOLOCK gives them.
Believe me, They have a lot bigger problems than not locking records. Having said that, their Lock Requests Per Sec, even with Read Uncommitted averages at 360,000 locks/sec. It averages at about 570 lock requests per second per batch request. At least that was before I recommended some basic actions that they implemented this weekend. I'm looking forward to see what things look like now, but I've not had a chance to check the impact of the changes yet.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply