SQLTeam.com | Weblogs | Forums

Avoid locks

I agree that you will, many times, need to introduce other features to solve certain problems. Just be aware the use of anything that allows dirty reads can cause some pretty serious errors because of the high concurrency that you're trying to solve, which is quite the irony.

Sorry Jeff but I try to undestand this aware to implement RCSI, it switch essentially from "pessimistic" concurrency (as the default in SQL Server) to optimistic concurrency same as Oracle database, so if Oracle use it as default (and Oracle is used in very high concurrency) why it could be cause pretty errors?
I see that SI could be dangerous for update conflict situation but RCSI?

Oracle and SQL Server are totally different engines and they operate in totally different manners.

With SQL Server (and to explain just one of the many problems), if you do dirty reads, it is actually possible to get both the original data and the new data as two separate rows. Such things can and do happen on heavy hit systems. You could also read uncommitted data that actually get's rolled back after your read it.

RCSI and all snapshot isolation doesn't dirty reads, see below msdn:

https://msdn.microsoft.com/en-us/library/ms378149(v=sql.110).aspx

Only transaction set to read uncommitted can do dirty reads.

What does the performance of a basically read/only snapshot have to do with any of this? Also, be VERY aware that snapshots slow down the main database (about 25% slower) even for just one active snapshot.

I'll say it again, the best defense against blocking is to have good fast code to begin with. All the tricks that people chose to try to avoid that fact all have costs that many aren't even aware of, never mind actually considering those costs.

What performance? Fix the bloody code because that's where performance, or the lack of it, lives. Yes, it's sometimes a stinker to fix but it's the only long lasting and scalable fix there is.

I had evaluate performance issue so I have to upgrade my server with SSD storage for tempdb and more memory ram for adapt to RCSI

More memory might help. There's also a good chance that moving TempDB to SSDs won't help as much as you might think since things in TempDB start in memory and only spool to disk if it runs out of room. It's better to fix the code.