070-762 試験問題を無料オンラインアクセス

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 view the Deadlock Graph as shown in the exhibit. (Click the Exhibit button.) Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.
NOTE: Each correct selection is worth one point.

正解：

Explanation

References: https://msdn.microsoft.com/en-us/library/ms186736.aspx

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.

正解：

Explanation

Box 1: sys.db_db_missing_index_group_stats
The sys.db_db_missing_index_group_stats table include the required columns for the main query:
avg_total_user_cost, avg_user_impact, user_seeks, and user scans.
Box 2: group_handle
Example: The following query determines which missing indexes comprise a particular missing index group, and displays their column details. For the sake of this example, the missing index group handle is 24.
SELECT migs.group_handle, mid.*
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
ON (mig.index_handle = mid.index_handle)
WHERE migs.group_handle = 24;
Box 3: sys.db_db_missing_index_group_stats
The sys.db_db_missing_index_group_stats table include the required columns for the subquery:
avg_total_user_cost and avg_user_impact.
Example: Find the 10 missing indexes with the highest anticipated improvement for user queries The following query determines which 10 missing indexes would produce the highest anticipated cumulative improvement, in descending order, for user queries.
SELECT TOP 10 *
FROM sys.dm_db_missing_index_group_stats
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC;

Your database contains a table named Invoices with a unique identifier column named InvoiceID.
You have a batch update process that reads data from an input file and updates the table.
You create a clustered columnstore index on the table and must update the configuration to meet the following requirements:
* The table must remain online.
* Perform analytic queries during business hours.
* Each row must have a unique key.
You need to configure the index to optimize performance.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

正解：

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_operational_stats dynamic management view.
Does the solution meet the goal?

• A.Yes
• B.No

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_usage_stats dynamic management view.
Does the solution meet the goal?

• A.Yes
• B.No