Discuss Microsoft DP-300 Exam Topic 8 Question 15

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.

AEnable Last_Query_Plan_Stats in the master database

BEnable Lightweight_Query_Profiling in DB1

CEnable Last_Query_Plan_Stats in DB1

DEnable Lightweight_Query_Profiling in the master database

EEnable PARAMETER_SNIFFING in DB1

Suggested Answer: A, C

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

Get Premium DP-300 Questions as Interactive Web-Based Practice Test or PDF Real Exam Format Detailed Explanations Instant Access Get Your Practice Test

14 comments · newest first

Edward 12 months ago

I agree, C and B seem like the right answers!

Gregg 1 year ago

Definitely not A or D, those won't help with DB1.

Portia 1 year ago

Wait, can you really enable profiling in the master database? Sounds odd.

Stevie 1 year ago

I think Lightweight_Query_Profiling in DB1 is also a good choice.

Justa 1 year ago

You need to enable Last_Query_Plan_Stats in DB1 for sure.

Arminda 1 year ago

I think enabling Last_Query_Plan_Stats in the master database is probably not the right approach for this scenario, but I need to double-check my notes on that.

Marti 1 year ago

I feel like I saw something about parameter sniffing in my studies, but I’m confused about whether it’s directly related to seeing past query parameters or just optimizing performance.

Barb 1 year ago

I remember practicing a question about query profiling, and I think Lightweight_Query_Profiling could be relevant here, but I can't recall if it needs to be enabled in the master database or the specific DB1.

Meaghan 1 year ago

I think enabling Last_Query_Plan_Stats in DB1 might be the right choice, but I'm not entirely sure if it captures the parameter values.

Antonio 1 year ago

This question seems straightforward. I'm pretty confident I can identify the two correct statements about the Event Processing Network (EPN) in Oracle Event Processing (OEP).

Ashlee 1 year ago

I think lower inventories are related to fast deliveries, but I'm not entirely sure if that's the right focus.

Rose 1 year ago

Okay, I've got a strategy for this. I'll start by considering the limitations of using ExpressRoute Edge Routers as a transit option.

Erick 1 year ago

Hmm, I'm a bit unsure about this one. I know R-squared is important, but I'm not sure about the other options. I'll have to think this through carefully.

Paz 1 year ago

I remember we discussed the differences between native and hybrid apps, but I can't recall which one fits here. Could it be hybrid?

Microsoft DP-300 Exam - Topic 8 Question 15 Discussion