Developing Microsoft SQL Server Databases — Question 4

You need to identify which long running transactions use an index.
Which dynamic management view should you use?

Answer options

• A. sys.dm_exec_query_optimizer_info
• B. sys.dm_exec_plan_attributes
• C. sys.dm_exec_requests
• D. sys.dm_exec_procedure_stats

Correct answer: A

Explanation

The correct answer is A, sys.dm_exec_query_optimizer_info, which provides insights into the behavior of the query optimizer, including information on long-running transactions that use indexes. The other options do not specifically target long-running transactions or provide index usage details; for example, B focuses on execution plan attributes, C deals with currently executing requests, and D provides statistics on stored procedures.