Microsoft Certified Azure Data Engineer Associate · Free Practice Question Medium
Question 49
Whilst Azure Synapse Analytics is used for the storage of data for analytical purposes, SQL Pools do support the use of transactions and adhere to the ACID (Atomicity, Consistency, Isolation, and Durability) transaction principles associated with relational database management systems.
As such, locking, and blocking mechanisms are put in place to maintain transactional integrity while providing adequate workload concurrency. These blocking aspects may significantly delay the completion of queries.
To improve the response time, turn [ ? ] the READ_COMMITTED_SNAPSHOT database option for a user database when connected to the master database.
-
A
READ_COMMITTED_SNAPSHOTis not the correct setting to adjust. -
B
OFF
-
C
ON
-
D
None of the listed options.
Reveal correct answer
Correct answer: C
Explanation
Whilst Azure Synapse Analytics is used for the storage of data for analytical purposes, SQL Pools do support the use of transactions and adhere to the ACID (Atomicity, Consistency, Isolation, and Durability) transaction principles associated with relational database management systems.
As such, locking, and blocking mechanisms are put in place to maintain transactional integrity while providing adequate workload concurrency. These blocking aspects may significantly delay the completion of queries. The isolation level of the transactional support is defaulted to READ UNCOMMITTED. You can change it to READ COMMITTED SNAPSHOT ISOLATION by turning ON the READ_COMMITTED_SNAPSHOT database option for a user database when connected to the master database.
Once enabled, all transactions in this database are executed under READ COMMITTED SNAPSHOT ISOLATION and setting READ UNCOMMITTED on session level will not be honoured.
If you experience delays in the completion of queries, the Read Committed Snapshot Isolation level should be employed to alleviate this. Read Committed Snapshot, makes a copy of the rows that are being referenced in a query if it is being updated, so that the data is consistent. The version of the data being used remains only for the duration of the query and any dependant queries, which are faster for query completion at the expense of space needed to storer multiple versions of the data during workloads.
To enable READ COMMITTED SNAPSHOT ISOLATION, run this command when connecting to the MASTER database.
- SQL
- ALTER DATABASE MyDatabase
- SET READ_COMMITTED_SNAPSHOT ON
A. READ_COMMITTED_SNAPSHOT is indeed the correct setting to adjust in this scenario. By enabling this option, you can enhance workload concurrency and minimize blocking, thereby improving the response time for queries in the user database connected to the master database.
B. Turning the READ_COMMITTED_SNAPSHOT database option OFF would not improve response time as it disables the row versioning feature that helps reduce blocking. This setting may lead to increased blocking and longer query completion times.
C. Turning the READ_COMMITTED_SNAPSHOT database option ON enables the database to use row versioning to provide transaction-level consistency for read operations. This can help reduce blocking and improve response time by allowing read operations to access the most recent committed version of data without being blocked by ongoing write operations.
D.
Turning the READ_COMMITTED_SNAPSHOT database option ON enables the database to use row versioning to provide transaction-level consistency for read operations. This can help reduce blocking and improve response time by allowing read operations to access the most recent committed version of data without being blocked by ongoing write operations.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
