Exam 70-764 topic 1 question 76 discussion

Actual exam question from Microsoft's 70-764

Question #: 76
Topic #: 1

You use Microsoft SQL Server 2016 to write code for a transaction that contains several statements.
There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space.
You also need to prevent reading queries from blocking writing queries.
Which isolation level should you use?

A. SERIALIZABLE
B. SNAPSHOT
C. READ COMMITTED SNAPSHOT
D. REPEATABLE READ

Show Suggested Answer

Suggested Answer: C 🗳️
For most applications, read committed isolation using row versioning is recommended over snapshot isolation for the following reasons:
It consumes less tempdb space than snapshot isolation.
Etc.
References: https://msdn.microsoft.com/en-us/library/ms188277.aspx

by TheSwedishGuy at Jan. 24, 2020, 2:43 p.m.

Comments

Submit Cancel

Iva10

4 years, 11 months ago

Snapshot isolation make pressure on tempdb, nad in the question is stated You need to minimize the use of the tempdb space. So the answer is maybe D repetable reads.

upvoted 2 times

Iva10

4 years, 11 months ago

https://www.sqlpassion.at/archive/2018/08/30/the-unwanted-side-effects-of-enabling-read-committed-snapshot-isolation/

upvoted 1 times

Iva10

4 years, 11 months ago

Wrong, the answer is Read commited Snapshot isolation, i didnt read the answer.

upvoted 3 times

Hoglet

4 years, 5 months ago

RCSI is less hard on tempdb than SI because it doesn’t need to keep the history for as long

upvoted 1 times

...

TheSwedishGuy

5 years, 5 months ago

The locks are avoided by returning the committed value record from the version store which is located in TempDB, instead of returning the values from the "live" table. Locking is therefore avoided.

upvoted 2 times

...