Provisioning SQL Databases — Question 29

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?

Answer options

• 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

Correct answer: C

Explanation

The correct choice is C, sys.dm_db_index_operational_stats, as it provides statistics about index usage and can help ascertain the impact of the newly imported rows on query performance. The other options do not specifically address execution plan issues: A pertains to transaction statistics, B relates to the input buffer for a session, and D gives information about statistics on columns, not execution plans.