Exam 70-762 topic 1 question 32 discussion

Actual exam question from Microsoft's 70-762

Question #: 32
Topic #: 1

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:

Show Suggested Answer

Suggested Answer:

Box 1: sys.db_db_missing_index_group_stats

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;

by stm22 at July 21, 2020, 11:09 p.m.

Comments

Submit Cancel

stm22

Highly Voted 4 years, 9 months ago

here are the clues: Box 1 whatever you choose for box 1 will be aliased "AS stats" thus we probably need to choose something with "stats" in the name (choices 1 or 3) the word "group" is everywhere in the query (choice 1!) Box 3: the order by fields at the bottom are exactly the same as the 2nd line at the top. must come from same table dm_db_missing_index_group_stats Box 2 the where clause above Box 2 tells us that the group_handle field is in stats, the alias for dm_db_missing_index_group_stats. "group_handle" is one of the choices

upvoted 10 times

...

azskierphoto

Most Recent 4 years, 4 months ago

SELECT so.name ,(avg_total_user_cost*avg_user_impact)*(user_seeks+user_scans) AS Impact ,mid.equality_columns ,mid.inequality_columns ,mid.included_columns FROM sys.dm_db_missing_index_group_stats AS stats INNER JOIN sys.dm_db_missing_index_groups AS mig ON stats.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle INNER JOIN sys.objects AS so ON mid.object_id = so.object_id WHERE stats.group_handle IN ( SELECT TOP (5000) group_handle FROM sys.dm_db_missing_index_group_stats ORDER BY (avg_total_user_cost*avg_user_impact)*(user_seeks+user_scans) DESC );

upvoted 1 times

...

Larzo776

4 years, 6 months ago

There should be a closing ")" at the end of this query; otherwise it will not run.

upvoted 1 times

Larzo776

4 years, 6 months ago

Either that, or it belongs immediately before the ORDER BY clause.

upvoted 1 times

...

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 32 discussion

Comments

stm22

azskierphoto

Larzo776

Larzo776

SY0-701