You want to identify the top 10 queries that have been recompiled the most times on your server. What can you do?
-
Use the sys.dm_exec_requests dynamic management view.
-
Use the sys.dm_exec_sessions dynamic management view.
-
Use the sys.dm_exec_query_stats dynamic management view.
-
Use the sys.dm_exec_query_optimizer_info dynamic management view.
The sys.dm_exec_query_stats DMV returns performance statistics for cached query plans, including a 'compilation_count' column that tracks how many times each query has been compiled. This allows identifying queries with the highest recompilation counts. The other DMVs focus on current requests, active sessions, and optimizer information respectively.
Correct answer: sys.dm_exec_query_stats. This SQL Server dynamic management view holds aggregate execution statistics per cached query plan, including a plan_generation_num column that increments each time a plan is recompiled — letting you sort/rank by that to find the most-recompiled queries. sys.dm_exec_requests and sys.dm_exec_sessions show currently executing requests/session info, not historical recompile counts, and sys.dm_exec_query_optimizer_info isn't a real DMV name (it's a distractor).