DRAG DROP
You are monitoring a Microsoft Azure SQL Database.
The database is experiencing high CPU consumption.
You need to determine which query uses the most cumulative CPU.
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL
segments to the correct locations. Each Transact-SQL segment may be used once, more than one or not at all.
You may need to drag the split bar between panes or scroll to view content.
Select and Place:

DRAG DROP
You are analyzing the performance of a database environment.
You suspect there are several missing indexes in the current database.
You need to return a prioritized list of the missing indexes on the current database.
How should you complete the Transact-SQL statement? To answer, drag the appropriate 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:

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?

HOTSPOT
You are maintaining statistics for a database table named tblTransaction. The table contains more than 10
million records.
You need to create a stored procedure that meets the following requirements:
– On weekdays, update statistics for a sample of the total number of records in the table.
– On weekends, update statistics by sampling all rows in the table.
A maintenance task will call this stored procedure daily.
How should you complete the stored procedure? To answer, select the appropriate Transact-SQL segments in
the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

HOTSPOTYou are analyzing the performance of a database environment.
You need to find all unused indexes in the current database.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL
segments in the answer area.
Hot Area:

DRAG DROP
You have a database named MyDatabase. You must monitor all the execution plans in XML format by using
Microsoft SQL Trace. The trace must meet the following requirements:
– Capture execution plans only for queries that run the MyDatabase database.
– Filter out plans with event duration of less than or equal to 100 microseconds.
– Save trace results to a disk on the server.
You need to create the trace.
In which order should you arrange the Transact-SQL segments to develop the solution? To answer, move all
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.
DECLARE @traceEventId int = 122;
DECLARE @traceColumnIdForTextData int = 1;
DECLARE @durationFilter bigint = 100
DECLARE @databaseID int;
SELECT @databaseId = DB_ID(‘MyDatabase’);
Select and Place:

HOTSPOT
You are reviewing the execution plans in the query plan cache. You observe the following:
– There are a large number of single use plans.
– There are a large number of simple execution plans that use multiple CPU cores.
You need to configure the server to optimize query plan execution.
Which two setting should you modify on the properties page for the Microsoft SQL Server instance? To answer,
select the appropriate settings in the answer area.
Hot Area:

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 is independent of the other
questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
– Return a value of 0 if data inserted successfully into the Customers table.
– Return a value of 1 if data is not inserted successfully into the Customers table.
– Support logic that is written by using managed code.
What should you create?

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 is independent of the other
questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
– Return a value of 0 if data inserted successfully into the Customers table.
– Return a value of 1 if data is not inserted successfully into the Customers table.
– Support TRY…CATCH error handling
– Be written by using Transact-SQL statements.What should you create?

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 is independent of the other
questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create a database object that meets the following requirements:
– Launch when table data is modified.
– Evaluate the state a table before and after a data modification and take action based on the difference.
– Prevent malicious or incorrect table data operations.
– Prevent changes that violate referential integrity by cancelling the attempted data modification.
– Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located
into Microsoft SQL Server.
What should you create?