Multiple choice technology databases

You want to identify the top 10 queries in SQL Server that have been recompiled the most times on your server. What can you do?

  1. Use the sys.dm_exec_requests dynamic management view.

  2. Use the sys.dm_exec_sessions dynamic management view

  3. Use the sys.dm_exec_query_stats dynamic management view

  4. Use the sys.dm_exec_query_optimizer_info dynamic management view
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The sys.dm_exec_query_stats DMV returns performance statistics for cached query plans, including columns like sql_handle, plan_handle, and execution_count that track recompilation events. The requests and sessions DMVs track current activity, while query_optimizer_info contains optimizer statistics but not per-query recompilation counts.

Keep practicing — related questions