Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:
* The table must reference the ProductID column in the Product table
* Existing records in the ProductReview table must not be validated with the Product table.
* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
* Changes to records in the Product table must propagate to the ProductReview table.
You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.
You must modify the Orders table to meet the following requirements:
* Create new rows in the table without granting INSERT permissions to the table.
* Notify the sales person who places an order whether or not the order was completed.
You must add the following constraints to the SalesHistory table:
* a constraint on the SaleID column that allows the field to be used as a record identifier
* a constant that uses the ProductID column to reference the Product column of the ProductTypes table
* a constraint on the CategoryID column that allows one row with a null value in the column
* a constraint that limits the SalePrice column to values greater than four Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.
You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:
* The table must hold 10 million unique sales orders.
* The table must use checkpoints to minimize I/O operations and must not use transaction logging.
* Data loss is acceptable.
Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.
You need to modify the design of the Orders table.
What should you create?

• A. a user defined function
• B. a FOR UPDATE trigger
• C. a stored procedure with the RETURN statement
• D. an AFTER UPDATE trigger

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

You are experiencing performance issues with the database server.
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.
What should you create?

• A. an Extended Events session that uses Query Editor
• B. a sys.dm_tran_database_transaction dynamic management view query
• C. an Activity Monitor session in Microsoft SQL Management Studio.
• D. a System Monitor report

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

You have a Microsoft Azure SQL database.
Some queries take a long time to execute.
You need to identify whether any of the long-runffiing queries prevent users from updating data in the database.
Which query should you execute? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Explanation

Box 1: sys.dm_tran_locks
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2017.
Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.
Box 2: sys.dm_os_waiting_tasks
sys.dm_tran_locks.lock_owner_address is the memory address of the internal data structure that is used to track this request. This column can be joined the with resource_address column in sys.dm_os_waiting_tasks.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks

You are designing a stored procedure for a database named obi.
The following requirements must be met during the entire execution of the stored procedure:
*The stored procedure must only read changes that are persisted to the database.
*select statements within the stored procedure should only show changes to the data that are made by the stored procedure You need to configure the transaction isolation level for the stored procedure.
Which Transact-SQL statement or statements should you run?
A)

B)

C)

D)

• A. Option B
• B. Option C
• C. Option D
• D. Option A

You have an existing Microsoft SQL Trace script. You plan to convert the script to an Extended Events session.
You need to collect the trace ID and other required information.
Which system table should you use?

• A. dbo. syinotifications
• B. dbo.syssessions
• C. sysdbmaintplan.
• D. trace xenction_map