April 8, 2021 at 3:24 pm
frederico_fonseca wrote:that is where some disagree.
on my 1500 production db's 220 have RCSI enabled - and most new db's are being created with it enabled unless the team requesting it ask for it to be disabled for some reason or if they are a copy from a system that had it disabled as on this case we don't wish raise potential issues.
Majority of cases having it enabled has little impact on the server from our own testing and benefits from a performance point of view outweighs that impact.
Oracle has it by default in any of their versions - don't even think it can be disabled.
RCSI has performance disadvantages UNLESS you're interested in accuracy. You can probably beat the snot out of RCSI with nolock queries LOL
Define "accuracy".
If I start a, say, sales report at 8AM, it shows data from 8AM only, even if it runs until, say, 2PM. Do the people looking at the report really realize that? That even if their report, their "bible", shows $2M in sales for a given region, it might really be $2.5M by then?
It's not necessarily automatic whether point-in-time numbers or numbers across time are better in such reports. And, again, the overhead of RCSI is enormous.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 8, 2021 at 3:48 pm
x wrote:frederico_fonseca wrote:that is where some disagree.
on my 1500 production db's 220 have RCSI enabled - and most new db's are being created with it enabled unless the team requesting it ask for it to be disabled for some reason or if they are a copy from a system that had it disabled as on this case we don't wish raise potential issues.
Majority of cases having it enabled has little impact on the server from our own testing and benefits from a performance point of view outweighs that impact.
Oracle has it by default in any of their versions - don't even think it can be disabled.
RCSI has performance disadvantages UNLESS you're interested in accuracy. You can probably beat the snot out of RCSI with nolock queries LOL
Define "accuracy".
If I start a, say, sales report at 8AM, it shows data from 8AM only, even if it runs until, say, 2PM. Do the people looking at the report really realize that? That even if their report, their "bible", shows $2M in sales for a given region, it might really be $2.5M by then?
It's not necessarily automatic whether point-in-time numbers or numbers across time are better in such reports. And, again, the overhead of RCSI is enormous.
I think we can certainly agree on what we disagree on!
April 9, 2021 at 11:27 am
I really don't want to get into an online p*****g match, however, I've also seen performance benefits from RCSI. Yes, management of the versions has overhead. No denying it. However, if it reduces the waits for reads on systems, you may see performance enhancements despite that overhead. Not every database will get this. Some will though.
"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
Viewing 3 posts - 46 through 47 (of 47 total)
You must be logged in to reply to this topic. Login to reply