70-762

70-762 practice questions, page 7 of 7, with answer review and explanations.

Question 31

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?

  1. Create a clustered indexon the table.
  2. Create a nonclustered index on the table.
  3. Create a nonclustered filtered index on the table.
  4. Create a clustered columnstore index on the table.
  5. Create a nonclustered columnstore index on the table.
  6. Create a hash index on the table.

Question 32

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:

Question 33

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:

Question 34

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:

Question 35

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?

  1. Start a SQL Server Profiler trace for the event class Auto Stats in the Performance event category.
  2. Create one Extended Events session with the sqlserver.missing_column_statistics event added.
  3. Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.
  4. Create one Extended Events session with the sqlserver.missing_join_predicate event added.