Exam 70-762 topic 1 question 172 discussion

Actual exam question from Microsoft's 70-762

Question #: 172
Topic #: 1

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

Show Suggested Answer

Suggested Answer: B 🗳️

by MML at July 17, 2020, 7:58 p.m.

Comments

Submit Cancel

Alex5x

4 years, 7 months ago

The right answer is B. To get an overview of which indexes are being used, you can use the sys.dm_db_index_usage_stats DMV. To appear in this DMV’s output, an index must be read or written to at least once by a user or system operation. To find the indexes which are never used you should retrieve all indexes in the database and then filter out those appearing in sys.dm_db_index_usage_stats.

upvoted 2 times

...

databasejamdown

4 years, 7 months ago

The answer is definitely no: see link https://sqldeep.com/usage-scenarios-for-sys-dm_db_index_operational_stats/ Where it says the following: The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used.

upvoted 1 times

...

MML

4 years, 11 months ago

This object is related to the sys.dm_db_index_usage_stats in that they both provide information about how the index is used. This object, however, gives more detailed information about the inner workings of how the index is used at a physical level, whereas the usage stats is more of a feel for how it is used by the optimizer to satisfy the needs of certain queries. Use usage stats if you want counts of each usage, as each usage counts as 1

upvoted 4 times

...