You need to create an Extended Events session to capture execution plans for queries that run for at least 10 minutes. The following requirements must be met:
The target must write complete buffers to disk asynchronously.
The system must retain a maximum of 10 files
Each session must allocate no more than 10 megabytes (MB) of memory for event buffering.
Which four 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.
Select and Place:
Answer is in the explanation below.
Reference / correct answer:
Step 1: CREATE EVENT SESSION SubOptimalExecPlans ON SERVER
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:
Answer is in the explanation below.
Reference / correct answer:
Box 1: sys.dm_exec_query_stats sys.dm_exec_query_stats returns aggregateperformance statistics for cached query plans in SQL Server.
Box 2: highest_cpu_queries.total_worker_time DESC Sort on total_worker_time column
Example: The following example returns information about the top five queries ranked by average CPU time.
Thisexample aggregates the queries according to their query hash so that logically equivalentqueries are grouped by their cumulative resource consumption.
USE AdventureWorks2012;
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time", MIN(query_stats.statement_text) AS "Statement Text"
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 named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.
The customer table includes a column that stores the data for the last order that the customer placed.
You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.
You need to modify the database design to meet the following requirements:
Rows in the Orders table must always have a valid value for the ProductID column.
Rows in the Products table must not be deleted if they arepart of any rows in the Orders table. All rows in both tables must be unique.
In the table below, identify the constraint that must be configured for each table.
NOTE: Make only one selection in each column.
Hot Area:
Answer is in the explanation below.
Reference / correct answer:
A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Here the foreign key constraint is put on the ProductID in the Orders, and points to the ProductID of the Products table.
With a check constraint on the ProductID we can ensure that the Products table contains only unique rows. References: http://www.w3schools.com/sql/sql_foreignkey.asp
Limited Time Offer – Save Now!
Don’t miss out — get full access at the best price.
The target must write complete buffers to disk asynchronously. 
