70 465 sample questions
Microsoft MCTS 70-465 : Designing Database Solutions for Microsoft SQL Server

70-465 Latest Exam Braindumps

Question 6: You need to implement changes to the system to reduce contention and improve
performance of the SalesOrderDetail table.
Which three actions should you perform? Each correct answer presents part of the solution. Choose
three.
A. Use (SNAPSHOT] hints in the report queries
B. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT ON
C. ALTER DATABASE [ProdDB] SET READ_COMMITTED_SNAPSHOT OFF
D. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
E. Use (TABLOCK) hints in the report queries
F. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
G. ALTER DATABASE [ProdDB] SET ALLOW_SNAPSHOT_ISOLATION ON
H. Use (SNAPSHOT] hints in the update statements
Correct Answer: A,B,F
Explanation:
*Scenario:
The SalesOrderDetail table holds the details about each sale. It is in the Sales schema
owned by the SalesStaff Windows group.
This table is constantly being updated, inserted into, and read.
*Regardless of which office runs a sales force report, the SalesOrderDetail table should only return
valid, committed order data; any orders not yet committed should be ignored.
*READ_COMMITTED_SNAPSHOT { ON | OFF } ON Enables Read-Committed Snapshot option at the
database level. When it is enabled, DML statements start generating row versions even when no
transaction uses Snapshot Isolation. Once this option is enabled, the transactions specifying the read
committed isolation level use row versioning instead of locking. When a transaction runs at the read
committed isolation level, all statements see a snapshot of data as it exists at the start of the
statement. OFF Turns off Read-Committed Snapshot option at the database level. Transactions
specifying the READ COMMITTED isolation level use locking. ALTER DATABASE SET Options (Transact-
SQL) SET Statements (Transact-SQL)