Free Preparation Discussions
MENU
Microsoft Exam DP-300 Topic 8 Question 15 Discussion
Topic #: 8
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.
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
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure
Currently there are no comments in this discussion, be the first to comment!