You are developing a database application by using Microsoft SQL Server 2012. An application that usesa
database begins to run slowly. You discover that during reads, the transaction experiences blocking from
concurrent updates. You need to ensure that throughout the transaction the data maintains the originalversion.
What should you do?
A.
Add a HASH hint to the query.
B.
Add a LOOP hint to the query.
C.
Add a FORCESEEK hint to the query.
D.
Add an INCLUDE clause to the index.
E.
Add a FORCESCAN hint to the Attach query.
F.
Add a columnstore index to cover the query.
G.
Enable the optimize for ad hoc workloads option.
H.
Cover the unique clustered index with a columnstore index.
I.
Include a SET FORCEPLAN ON statement before you run the query.
J.
Include a SET STATISTICS PROFILE ON statement before you run the query.
K.
Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L.
Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the
query.
M.
Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N.
Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Show Answer on this one doesn’t work.
The answer is between L, M, N. For sure any of this Isolation levels guarantees the data will remain without modifications (althought REPETEABLE READ may suffer from PHANTOM READS) until the end of the transaction. But the scenario stated a blocking problem between writers and readers.Having that in consideration REPEATEABLE READ and SERIALIZABLE will most likely aggravate the problem because they use SHARED LOCKS. SNAPSHOT on the other hand uses ROW VERSIONING, so the readers will get the data from a committed copy in TEMPDB, avoiding locking problems. In conclusion I think the correct answer is M. SET TRANSACTION ISOLATION LEVEL SNAPSHOT.
it’s M the snapshot.
Answer is M
http://www.mcsaquestion.com/latest-mcse-70-461-real-exam-download-61-70.html
The Answer is between M,N because these isolation levels are the maximum that prevent phantom(insert new row) and update date in the exciting rows
But there is no command says set transaction isolation level snapshot you must
1- Alter the database
2- “SET ALLOW_SNAPSHOT_ISOLATION ON”
so the correct answer is N
M snapshot
N