Exam 70-762 topic 1 question 39 discussion

Actual exam question from Microsoft's 70-762

Question #: 39
Topic #: 1

HOTSPOT -
You 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:

Show Suggested Answer

Suggested Answer:

Example: Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV.
-- Ensure a USE statement has been executed first.
SELECT u.*

FROM [sys].[indexes] i -
INNER JOIN [sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP'
AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
AND u.[last_system_scan] IS NOT NULL

ORDER BY 1 ASC -
References:
https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/

by RobKozak at Jan. 20, 2020, 9:05 p.m.

Comments

Submit Cancel

RobKozak

Highly Voted 5 years, 5 months ago

Based off the field aliases in the select statement I would expect table A (3rd pick list) to be sys.tables. It appears that the only purpose A serves is to retrieve the table that the index is on. The way that the query is built it will return the index name twice.

upvoted 28 times

Nelly100

5 years, 3 months ago

Yes that's correct. The third box should be Sys.Tables thats the only place where tableName comes from based on their select statement

upvoted 8 times

...

ARehman

4 years, 7 months ago

Correct !

upvoted 1 times

...

delgadillo

Highly Voted 5 years ago

the answers is : 1.sys.indexes 2.sys.dm_db_index_usage_stats 3.sys.tables select a.name as tablename , b.name as indexname from sys.indexes b inner join sys.dm_db_index_usage_stats s on s.object_id=b.object_id and s.index_id=b.index_id inner join sys.tables a on b.object_id=a.object_id where ((user_seeks=0 and user_scans=0 and user_lookups=0) or s.object_id is null) --- se usan la tabla B

upvoted 25 times

...