Note: The 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
question in the series. Information and details provided in a question apply only to that question.
You have a database named DB1. The database does not have a memory optimized filegroup.
You create a table by running the following Transact-SQL statement:

The table is currently used for OLTP workloads. The analytics user group needs to perform real-time
operational analytics that scan most of the records in the table to aggregate on a number of columns.You need to add the most efficient index to support the analytics workload without changing the OLTP
application.
What should you do?

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:

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 memory usage of a Microsoft SQL Server instance.
You need to obtain the information described on the following table.

Which performance counter should you use for each requirement? To answer, drag the appropriate
performance counters to the correct requirements. Each performance counter may be used once, more than
once or not at all. You may need to drag the split bat between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the
following issues:
– At each level of the query plan, a low total number of rows are processed.
– The query uses many operations. This results in a high overall cost for the query.
You need to identify the information that will be useful for the optimizer.
What should you do?

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:

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:

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:

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: