Which two database settings should you modify?

HOTSPOT

You have a server named SQL1 that hosts a reporting database named REPORTING. REPORTING contains data that is also stored in a production database.

You discover many blocking locks in REPORTING. The blocked queries are SELECT statements. The queries that hold the blocking locks are INSERT statements that wait on the WRITELOG wait type.

You need to prevent the INSERT statements from creating the blocking locks. The solution must also minimize the amount of time the INSERT statements wait on the WRITELOG wait type.

Which two database settings should you modify? To answer, select the appropriate settings in the answer area.

View Answer

Answer:

Explanation:

Is Read Committed Snapshot On: True

Snapshot isolation enhances concurrency for OLTP applications.

Understanding Snapshot Isolation and Row Versioning

Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.

The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot­isolation-in-sql-server