Multiple choice technology databases

You want to identify the top 10 queries 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

sys.dm_exec_query_stats provides performance statistics for cached query plans, including execution counts and recompilation counts. This DMV allows identifying queries with high recompilation rates. sys.dm_exec_requests shows currently executing queries, sys.dm_exec_sessions shows session info, and sys.dm_exec_query_optimizer_info shows optimizer metrics.

Keep practicing — related questions