【DeadLock&ISOLATION LEVELに関する問題】
1.
You have a relational data warehouse that contains 1 TB of data.
You have a stored procedure named usp_sp1 that generated an HTML fragment. The HTML fragment contains color and font style.
You need to return the HTML fragment.
What should you do?
A. Use the NOLOCK option.
B. Execute the DBCC UPDATEUSAGE statement.
C. Use the max worker threads option.
D. Use a table-valued parameter.
E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
F. Set SET XACT_ABORT to ON.
G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
H. Use the OUTPUT parameters.
Correct Answer: G
A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application.
2.
Your company runs end-of-the-month accounting reports. While the reports run, other financial records are updated in the database.
Users report that the reports take longer than expected to run.
You need to reduce the amount of time it takes for the reports to run. The reports must show committed data only.
What should you do?
A. Use the NOLOCK option.
B. Execute the DBCC UPDATEUSAGE statement.
C. Use the max worker threads option.
D. Use a table-valued parameter.
E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
F. Set SET XACT_ABORT to ON.
G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
H. Use the OUTPUT parameters.
Correct Answer: E
Snapshot isolation enhances concurrency for OLTP applications.
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.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
3.
You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.
A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.
You need to change the dashboard queries to improve concurrency and to support reading uncommitted data.
What should you do?
A. Use the NOLOCK option.
B. Execute the DBCC UPDATEUSAGE statement.
C. Use the max worker threads option.
D. Use a table-valued parameter.
E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
F. Set SET XACT_ABORT to ON.
G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
H. Use the OUTPUT parameters.
Correct Answer: A
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
This can improve query performance, but also introduces the possibility of dirty reads.
Incorrect Answers:
F: When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
G: DISABLE, not AUTO, would be better.
There are two more lock escalation modes: AUTO and DISABLE.
The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE.
The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.
Note: SQL Server's locking mechanism uses memory resources to maintain locks. In situations where the number of row or page locks increases to a level that decreases the server's memory resources to a minimal level, SQL Server's locking strategy converts these locks to entire table locks, thus freeing memory held by the many single row or page locks to one table lock. This process is called lock escalation, which frees memory, but reduces table concurrency.
References:
https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
4.
You have multiple stored procedures inside a transaction.
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?
A. Use the NOLOCK option.
B. Execute the DBCC UPDATEUSAGE statement.
C. Use the max worker threads option.
D. Use a table-valued parameter.
E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
F. Set SET XACT_ABORT to ON.
G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
H. Use the OUTPUT parameters.
Correct Answer: F
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017
5.
You have a nonpartitioned table that has a single dimension. The table is named dim.Products.Projections.
The table is queried frequently by several line-of-business applications. The data is updated frequently throughout the day by two processes.
Users report that when they query data from dim.Products.Projections, the responses are slower than expected. The issue occurs when a large number of rows are being updated.
You need to prevent the updates from slowing down the queries.
What should you do?
A. Use the NOLOCK option.
B. Execute the DBCC UPDATEUSAGE statement.
C. Use the max worker threads option.
D. Use a table-valued parameter.
E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
Correct Answer: A
The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.
This can improve query performance, but also introduces the possibility of dirty reads.
References:
https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
6.
...ここにあったバックグラウンド等は関係なさそうなので割愛してます。
You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.
You need to generate the reports more quickly. The database must not use additional resources.
What should you do?
A. Update the transaction level of the report query session to READPAST.
B. Modify the report queries to use the UNION statement to combine the results of two or more queries.
C. Set the READ_COMMITTED_SNAPSHOT database option to ON.
D. Update the transaction level of the report query session to READ UNCOMMITTED.
E. Apply a nonclustered index to all tables used in the report queries.
Correct Answer: D
Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. This is the least restrictive of the isolation levels.
References:
https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx
6.
You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1.
The environment is configured with the following properties:
✑ The database is configured with the default isolation setting.
✑ The application and process use the default transaction handling.
You observe the application cannot access any rows that are in use by the process.
You have the following requirements:
✑ Ensure the application is not blocked by the process.
✑ Ensure the application has a consistent view of the data
✑ Ensure the application does not read dirty data.
You need to resolve the issue and meet the requirements with the least amount of administrative effort.
What should you do?
A. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application for the SERIALIZABLE isolation level.
B. Enable the database for the READ_COMITTED_SNAPSHOT isolation level.
C. Enable the application for the WITH (NOLOCK) hint.
D. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application and the update process for the SNAPSHOT isolation level.
Correct Answer: B
Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb).
READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.
Incorrect Answers:
A: SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
7.
You are creating the following two stored procedures:
✑ A natively-compiled stored procedure
✑ An interpreted stored procedure that accesses both disk-based and memory-optimized tables
Both stored procedures run within transactions.
You need to ensure that cross-container transactions are possible.
Which setting or option should you use?
A. the SET TRANSACTION_READ_COMMITTED isolation level for the connection
B. the SERIALIZABLE table hint on disk-based tables
C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database
Correct Answer: C
Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory- optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.
Incorrect Answers:
B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017
8.
You are designing a stored procedure for a database named DB1.
The following requirements must be met during the entire execution of the stored procedure:
The stored procedure must only read changes that are persisted to the database.
SELECT statements within the stored procedure should only show changes to the data that are made by
the stored procedure.
You need to configure the transaction isolation level for the stored procedure.
Which Transact-SQL statement or statements should you run?
A. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
ALTER DATABASE DB1 SET READ_COMMITED_SNAPSHOT ON
B. SET TRANSACTION ISOLATION LEVEL READ COMMITED
ALTER DATABASE DB1 SET READ_COMMITED_SNAPSHOT OFF
C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
D. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
ALTER DATABASE SET READ_COMMITED_SNAPSHOT OFF9.
Correct Answer: B
Explanation
Explanation/Reference:
READ COMMITTED specifies that statements cannot read data that has been modified but not committed
by other transactions. This prevents dirty reads. Data can be changed by other transactions between
individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This
option is the SQL Server default.
Incorrect Answers:
A, D: READ UNCOMMITTED specifies that statements can read rows that have been modified by other
transactions but not yet committed.
References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sqlserver
9.
You manage a database that supports an Internet of Things (IoS) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995% uptime.
The database uses a table named Checkins that is 100 gigabytes (GB) in size. The Checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use stored procedures for all access to the tables.
You observe that the wait type PAGELATCH_IO causes large amounts of blocking.
You need to resolve the blocking issues while minimizing downtime for the database.
Which two actions should you perform? Each correct answer presents part of the solution.
A. Convert all stored procedures that access the Checkins table to natively compiled procedures.
B. Convert the Checkins table to an In-Memory OLTP table.
C. Convert all tables to clustered columnstore indexes.
D. Convert the Checkins table to a clustered columnstore index.
Correct Answer: AB
Natively compiled stored procedures are Transact-SQL stored procedures compiled to native code that access memory-optimized tables. Natively compiled stored procedures allow for efficient execution of the queries and business logic in the stored procedure.
SQL Server In-Memory OLTP helps improve performance of OLTP applications through efficient, memory-optimized data access, native compilation of business logic, and lock- and latch free algorithms. The In-Memory OLTP feature includes memory-optimized tables and table types, as well as native compilation of
Transact-SQL stored procedures for efficient access to these tables.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/natively-compiled-stored-procedures https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables
10.
You have a table that stores transactions partitioned by year. Users occasionally experience performance issues when they access the table. The table is locked exclusively when the records are updated. You need to prevent exclusive locks on the table and maintain data integrity.
What should you do?
A. Set the LOCK_EXCALATION property to DISABLE
B. Set the DATA_COMPRESSION property to ROW at the partition level
C. Set the LOCK_EXCALATION property to AUTO
D. Set the LOCK_EXCALATION property to TABLE
E. Set the DATA_COMPRESSION property to PAGE at the partition level
Correct Answer: C
The default lock escalation mode is called TABLE, it implements SQL Server's lock escalation on all types of tables whether partitioned or not partitioned.
There are two more lock escalation modes: AUTO and DISABLE.
✑ The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE.
✑ The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.
References:
https://www.mssqltips.com/sqlservertip/4359/altering-lock-escalation-for-sql-server-tables/
