Which two actions should you perform?

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.
A . Enable Last_Query_Plan_Stats in the master database
B . Enable Lightweight_Query_Profiling in DB1
C . Enable Last_Query_Plan_Stats in DB1
D . Enable Lightweight_Query_Profiling in the master database
E . Enable PARAMETER_SNIFFING in DB1

View Answer

Answer: B, E

Explanation:

Last_Query_Plan_Stats allows you to enable or disable collection of the last query plan statistics (equivalent to an actual execution plan) in sys.dm_exec_query_plan_stats.

Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = OFF;.

Incorrect Answers:

E: Parameter sensitivity, also known as "parameter sniffing", refers to a process whereby SQL Server "sniffs" the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer so that they can be used to generate potentially more efficient query execution plans.

Parameter values are sniffed during compilation or recompilation for the following types of batches:

Stored procedures

Queries submitted via sp_executesql

Prepared queries

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-

infrastructure