DP-300 Certification Practice Question #164

Question

You have SQL Server on an Azure virtual machine that contains a database named DB1.
You have an application that queries DB1 to generate a sales report.
You need to see the parameter values from the last time the query was executed.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

Accepted Answer

To view the parameter values from the last time a query was executed, you must capture the actual execution plan of the query. The `sys.dm_exec_query_plan_stats` dynamic management function returns the last known actual execution plan (which includes parameter values in the XML representation). This function requires both `LAST_QUERY_PLAN_STATS` (Option C) and the lightweight query profiling infrastructure (`LIGHTWEIGHT_QUERY_PROFILING`, Option B) to be enabled. Since the target database is DB1, these database-scoped configurations must be enabled on DB1, not the master database (Options A and D). `PARAMETER_SNIFFING` (Option E) controls whether SQL Server compiles query plans using parameters, but does not collect or expose execution statistics.

More DP-300 practice questions