Exam 70-765 topic 3 question 105 discussion

Actual exam question from Microsoft's 70-765

Question #: 105
Topic #: 3

You have a database named DB1 that contains a table named Table1. Table1 has 1 billion rows.
You import 10 million rows of data into Table1.
After the import, users report that queries take longer than usual to execute.
You need to identify whether an out-of-date execution plan is causing the performance issue.
Which dynamic management view should you use?

A. sys.dm_xtp_transaction_stats
B. sys.dm_exec_input_buffer
C. sys.dm_db_index_operational_stats
D. sys.dm_db_stats_properties

Show Suggested Answer

Suggested Answer: C 🗳️
Expalantion:
sys.dm_db_index_operational_stats dynamic management function provides us the current low-level I/O, locking, latching, and access method for each partition of the table. This information is really useful to troubleshoot SQL Server performance issues.
Reference: https://basitaalishan.com/2013/03/19/using-sys-dm_db_index_operational_stats-to-analyse-how-indexes-are-utilised/

by MelKr at May 18, 2020, 3:40 p.m.

Comments

Submit Cancel

MelKr

Highly Voted 5 years, 1 month ago

Answer D (sys.dm_db_stats_properties) is better to find out if execution plan is built on current information, e.g. statistics.

upvoted 5 times

KC

5 years ago

Agreed. stats_properties gives specific information relevant to the statistics being current or out of date by showing the last_updated information.

upvoted 1 times

...

Haidardba

4 years, 5 months ago

You can use sys.dm_db_index_operational_stats to track the length of time that users must wait to read or write to a table, index, or partition, and identify the tables or indexes that are encountering significant I/O activity or hot spots. https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-operational-stats-transact-sql?view=sql-server-ver15

upvoted 1 times

munro1900

4 years, 5 months ago

True, but that dm is used to check, as you said, bottlenecks for I/O activity while the question mentions out-of-date exec plans... answer for this has to be sys.dm_db_stats_properties.

upvoted 1 times

...