ExamTopics Logo
Mail Us[email protected]
Menu
Microsoft Discussions
exam questions

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam
Go to Exam

Exam 70-762 topic 1 question 39 discussion

Actual exam question from Microsoft's 70-762
Question #: 39
Topic #: 1
[All 70-762 Questions]

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 Hide 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

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
RobKozak
Highly Voted 5 years, 3 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, 1 month 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, 5 months ago
Correct !
upvoted 1 times
...
...
delgadillo
Highly Voted 4 years, 10 months 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
...
AJ_96
Most Recent 5 years, 2 months ago
1st box: sys.indexes 2nd box: sys.tables don't even debate the answer.
upvoted 4 times
Nelly100
5 years, 1 month ago
You are wrong, the 3rd box should be sys.tables not 2nd box!!
upvoted 9 times
...
Froze
5 years, 1 month ago
There is no reason to try. Try to find out index_id column in sys.tables and you will realize that you were wrong.
upvoted 1 times
...
Andy7622
4 years, 7 months ago
box 2 has alias s that's not the sys.tables
upvoted 1 times
...
databasejamdown
4 years, 6 months ago
You are clearly wrong about 2nd box
upvoted 1 times
...
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago