DP-300 Certification Practice Question #176

Question

You have an Azure SQL managed instance.
You need to gather the last execution of a query plan and its runtime statistics. The solution must minimize the impact on currently running queries.
What should you do?

Accepted Answer

To retrieve the last actual execution plan and its runtime statistics with the minimum performance impact on running workloads, you should query the `sys.dm_exec_query_plan_stats` dynamic management function (Option C). This function utilizes lightweight query profiling, which is enabled by default in modern SQL versions and incurs negligible performance overhead (typically less than 2%). In contrast, generating actual execution plans (Option B) or using Live Query Statistics (Option D) requires running the actual queries with heavy profiling options enabled, creating a significant impact on database performance. Estimated execution plans (Option A) do not contain the required runtime statistics.

More DP-300 practice questions