Case study -- About deadlock
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.
Question 1:
Both of the stored procedures experience blocking issues. UspB must not abort if UspA commits changes to a row before UspB commits changes to the same row. UspA must not abort if UspB commits changes to a row before UspA commits changes to the same row. You need to specify the transaction isolation levels to enable row versioning.
How should you complete the Transact-SQL statements? To answer, drag the Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. Select and Place:
References: https://technet.microsoft.com/en-us/library/ms175095(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx
Answer Given:
Question 2:
Users must only be able to modify data in the Employee table by using the vwEmployee view. You must prevent users from viewing the view definition in catalog views. You need to identify the view attribute to use when creating vwEmployee. In the table below, identify the attributes that you must use. NOTE: Make only one selection in each column.
Reference: https://msdn.microsoft.com/en-us/library/ms187956.aspx
Discussion:
New_user
Please explain why use check option and encryption?
Gocsan
There is no meaningful answer here. The question states select one answer from each column. But the answer area contains multiple selection from each column.
bsciprian
Update restriction: check option (it's about restricting) and restrict access to definition: with encryption.
Question 3:
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.
References: https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx
Answer Given: 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.
Question 4:
You view the Deadlock Graph as shown in the exhibit. Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic. NOTE: Each correct selection is worth one point.
References: https://msdn.microsoft.com/en-us/library/ms186736.aspx
Answer Given:
Discussion:
EdwardWang
An Exclusive lock
Heisenberg008
For second part the answer would be “an exclusive lock” due to request mode “X” from process ID 56. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql
Question 5:
You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using. You need to create a single index to replace the existing indexes. Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order. NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select. Select and Place:
Scenario: You observe that the four indexes require a large amount of disk space. You must reduce the amount of disk space that the indexes are using. Current indexes:
Answer Given:
Discussion:
New_user
Why don't use clustered index?
BruceWayne
because the primary key already create a clustered index on the table
Zikato
This solution doesn't include DepartmentId in the key columns as the Index1 does.The correct solution cannot be pieced together from these options.
Bartek
"More than one answer is correct..." Can be (Lastname, Firstname) INCLUDE (HireData, DepartmentId, EmployeeID) OR (Firstname, Lastname, DeparemtntID) INCLUDE (HireData, DepartmentId)
Kuratko
CREATE INDEX IX_New ON dbo.Employee (LastName, FirstName, DepartmentId) INCLUDE (HireDate) -> Column name 'DepartmentId' can be listed once ONLY; EmpleyeeID IS included AS relation to PRIMARY KEY, so it can but NOT REQUIRED IN INCLUDE Part. -> KEEP IX_1 AND DROP the others:) Aggree WITH Zikato. (Firstname, Lastname, DeparemtntID) INCLUDE (HireData, DepartmentId) --> have you try to run it?
moehijawe
since our cluster index on Employee_ID so all non cluster indexes have this column inside so include should be : INCLUDE (HireData, DepartmentId)