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 user 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.
Explanation:
https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx
I would go for “D. Update the transaction level of the report query session to READ UNCOMMITTED.”
It is not clear if allow_snapshot isolation is on.
We should minimize “the amount of storage”, and in snapshot we will use some for “row versioning”.
The reports are “read-only” so there is no versions of rows.
15
4
“You need to generate the reports more quickly.”, that means D because READ_COMMITTED_SNAPSHOT even slower than READ_COMMITTED if we forget blocking issues. question never show we need avoid dirty read. READ UNCOMMITTED is quicker method to get results.”The database must not use additional resources.” means we cannot use READ_COMMITTED_SNAPSHOT because it will use more CPU and tempdb results.
14
0
Definitely C
Thanks to
https://www.red-gate.com/simple-talk/sql/t-sql-programming/row-versioning-concurrency-in-sql-server/
0
13
Lol you article says Row versioning requires additional resources in tempdb.
6
2
Sorry i meant i agreed with you definitely C
1
8
I mean D Row versioning requires additional resources in tempdb so answer is D
12
1