Exam 70-762 topic 1 question 84 discussion

Actual exam question from Microsoft's 70-762

Question #: 84
Topic #: 1

Background -
You have a database named HR1 that includes a table named Employee.
You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.

Employee Table -
You use the following Transact-SQL statements to create, configure, and populate the Employee table:

Application -
You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
✑ UspA: This stored procedure updates only the EmployeeStatus column.
✑ UspB: This stored procedure updates only the EmployeePayRate column.
The application uses views to control access to data. Views must meet the following requirements:
✑ Allow users access to all columns in the tables that the view accesses.
✑ Restrict updates to only the rows that the view returns.

Exhibit -

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.

Show Suggested Answer

Suggested 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

by ziggyz at Aug. 13, 2020, 10:25 a.m.

Comments

Submit Cancel

ziggyz

Highly Voted 4 years, 10 months ago

READPAST will skip locked rows completely. UNION will make no difference to concurrency. READ_COMMITTED_SNAPSHOT will assist with concurrency, but will use additional memory resources, and the "database must not use additional resources". NONCLUSTERED INDEX will also assist with concurrency, but will use additional disk space, and "you must minimize the amount of storage that the database requires". READ UNCOMMITTED can result in dirty reads, but the reports being run "use multiple queries to estimate payroll expenses". As reports are estimates only, dirty reads are acceptable. READ UNCOMMITTED is the correct answer.

upvoted 28 times

...