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.
BC
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:
A: Enable it for DB1, not for the master database.
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 https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure
I think answer given is correct. Ref: https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure?view=sql-server-ver16
It is correct, B is obviously first choice but Query Plan can be used for same In the query plan XML (from the query_plan column), look for the ParameterCompiledValue node to find the compiled parameter value
Chat says B, E: the correct answers are B (Enable Lightweight_Query_Profiling in DB1) and E (Enable PARAMETER_SNIFFING in DB1). These actions will help you capture and analyze parameter values for your sales report queries.
Not sure if "C. Enable Last_Query_Plan_Stats in DB1" would capture anything related to parameters.
Is it ok?