dirty select and snapshot isolation

Question

Post reply

dirty select and snapshot isolation

abdalah.mehdoini

Ten Centuries

Points: 1012
More actions
January 17, 2022 at 11:36 am

#3973840
Hello everyone
Can anyone explain the difference to me please btewwen
```
ALTER DATABASE test  SET READ_COMMITTED_SNAPSHOT ON
```
and
```
ALTER DATABASE [test] SET [READ_UNCOMMITTED] ON GO
```
because in both cases my select works well even if my update is still running
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

How correct do you need your results to be ?

Check this great blog post by Paul White: "The Read Uncommitted Isolation Level"

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

The first big difference is that the second command is not valid, and thus won't run and therefore won't do anything.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

Here's the Microsoft documentation

Read uncommitted simply doesn't take out some locks, allowing for reads as data gets changed. This can result in incorrect data, missing or duplicate data. Honestly, a scary proposition if you're working for any organization that needs accurate information.

Read committed snapshot creates, effectively, a copy of the data while it updates it. That data can be read. You may get inconsistent results ('cat' in one run, 'dog' in another), but you won't see missing or duplicate rows as you will with read uncommitted.

There are overheads with read committed snapshot, but it's the safer approach.

"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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

IIRC, RCSI adds 14 bytes to every row and that's likely going to cause a 50% page density and a high degree of logical fragmentation to be present right after implementation. You should include time to rebuild affected indexes (especially Clustered Indexes) for your implementation. Not sure because I've not personally had to implement RCSI but it may be better to create new tables and copy the data to them then rename the tables and drop the old ones. Of course, backups should be on that agenda, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

Here is a nice reference by Brent Ozar regarding RCSI: "Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide"

Have a look at the "Gotchas With Implementing Snapshot and Read Committed Snapshot Isolation Levels"

Same goes for "NOLOCK Is Bad And You Probably Shouldn’t Use It."

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Johan Bijnens wrote:

Here is a nice reference by Brent Ozar regarding RCSI: "Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide"
Have a look at the "Gotchas With Implementing Snapshot and Read Committed Snapshot Isolation Levels"
Same goes for "NOLOCK Is Bad And You Probably Shouldn’t Use It."

Great link on the subject, Johan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

Grant Fritchey wrote:

Read uncommitted (RU) simply doesn't take out some locks, allowing for reads as data gets changed. This can result in incorrect data, missing or duplicate data. Honestly, a scary proposition if you're working for any organization that needs accurate information.
Read committed snapshot (RCSI) creates, effectively, a copy of the data while it updates it. That data can be read. You may get inconsistent results ('cat' in one run, 'dog' in another), but you won't see missing or duplicate rows as you will with read uncommitted.
There are overheads with read committed snapshot, but it's the safer approach.

Let's be fair and complete: the READ COMMITTED (RC), the default isolation level, can also miss data and/or return duplicate data. It does not, however, return "dirty"/in-flight data.

Snapshot returns data at the point in time your query started. If that is what you want, it's great. If not, maybe not.

For example, assume a report starts at 9:10AM and doesn't complete until 9:26AM. ALL data on the report will be as of 9:10AM. But sometimes you might want to see the more recent data.

For example, assume a customer's rep changed at 9:15AM. When the report lists that customer at 9:26AM, do you want to see the new rep or the old rep? Most business folks would likely want to see the new rep. RCSI would always show the old rep. Typically RC or RU would show the new rep (although not guaranteed to).

And the same applies to any other data that changes during that time. Obviously the longer the transaction takes, the more such "old" data will be SELECTed.

Unquestionably RCSI offers many advantages. But the overhead is very significant because all the "old" data must be saved in tempdb until it is no longer needed. And for ALL dbs using RCSI. Keep that in mind if you chose to use RCSI. Also, you need to understand how it changes query results.

In our case, we have hundreds and hundreds of dbs, so I'm very selective about which dbs I set to RCSI. I've got some, but they are carefully chosen. In my environment, I can't just apply it to all, or even the majority, of my dbs. The overhead would be just far too great. (Besides, if we wanted Oracle, we'd have gotten Oracle! Although I'm virtually certain Oracle now has a way to avoid using their equivalent of RCSI, in the old days it did not. That overhead was forced on you.)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".