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_optimizer_info dynamic management view

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

The sys.dm_exec_query_stats Dynamic Management View returns performance statistics for cached query plans. It includes the plan_generation_num column, which increments each time a query is recompiled, allowing you to identify top recompiled queries. Other views like sys.dm_exec_requests monitor active sessions.

Keep practicing — related questions