You have a database named Database1.
Users report that they experience deadlock issues. You run the sp_readerlog stored procedure. You view the output from the Process List section as shown in the Process List exhibit. (Click the Process List tab.)

You view the contents of the Resource List section as shown in the Resource List exhibit. (Click the Resource List tab.)

You view deadlock information as shown in the Deadlock List exhibit. (Click the Deadlock List tab.)

You need to identify the causes of the deadlock.
For each of the following statements, select Yes if the statement is true. Otherwise, select No.

Explanation

Box 1: No

The following three lock types are used for row- and page-level locking:
Shared (S)
Exclusive (X)
Update (U)
An exclusive lock reserves a page or row, but not a whole table, for the exclusive use of a single transaction.
Note: Read committed specifies that statements cannot read data that has been modified but not committed by other transactions.
Box 2: No
Taskpriority is 0, which is the default priority. The transaction could be chosen as the victim if other transactions have a priority higher than 0.

Box 3: Yes

References:
https://www.sqlshack.com/what-is-a-sql-server-deadlock/
https://logicalread.com/sql-server-lock-modes-mc03/#.XULCdm8zaUk

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. Modify the report queries to use the UNION statement to combine the results of two or more queries.
• B. Update the transaction level of the report query session to READPAST.
• C. Update the transaction level of the report query session to READ UNCOMMITTED.
• D. Set the READ_COMMITTED_SNAPSHOT database option to ON.

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Note: This question is part of a series of questions that use the same or similar answer choices. An Answer choice may be correct for more than one question in the series. Each question independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You observe that many deadlocks appear to be happening during specific times of the day.
You need to monitor the SQL environment and capture the information about the processes that are causing the deadlocks.
What should you do?

• A. Create a sys.dm_exec_sessions query.
• B. Create a SQL Profiler trace.
• C. Create a PerformanceMonitor Data Collector Set.
• D. Create a sys.dm_os_memory_objects query.
• E. Create a sp_configure 'max server memory' query.
• F. Create a sys.dm_os_waiting_tasks query.
• G. Create an Extended Event.
• H. Create a sys.dm_os_wait_stats query.

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

You have a memory-optimized table named Customer. The table is accessed by a stored procedure named ManageCustomer.
The database was created in Microsoft SQL Server 2014. A backup and restore operation was used to move the database to SQL Server 2016.
You have performance issues with the stored procedure.
You need to resolve the performance issues and ensure the table statistics are updated automatically.
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.

Explanation

Step 1: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL
Sets certain database behaviors to be compatible with the specified version of SQL Server.
Syntax:
ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
Step 2: UPDATE STATISTICS Customers
UPDATE STATISTICS updates query optimization statistics on a table or indexed view.
Step 3: EXEC sp_recompile N' 'Manage_customer'
sp_recompile causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level
https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

You have a database with multiple tables. You must insert data into the tables by using views.
Which of the following two statements best describes an updatable view?
NOTE: Each correct selection is worth one point.

• A. The view may include a composite field.
• B. The view may include GROUP BY or HAVING statements.
• C. Each view must reference columns from one table.
• D. The view must not include the primary key of the table.
• E. The view must not include subqueries.

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).