Microsoft DP-300 Exam - Topic 8 Question 15 Discussion

Actual exam question for Microsoft's DP-300 exam

Question #: 15
Topic #: 8

[All DP-300 Questions]

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

Show Suggested Answer

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

by Nan at May 04, 2022, 03:38 PM

Limited Time Offer

25%

Off

Get Premium DP-300 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Edward

4 months ago

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

upvoted 0 times

...

Gregg

4 months ago

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

upvoted 0 times

...

Portia

4 months ago

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

upvoted 0 times

...

Stevie

5 months ago

I think Lightweight_Query_Profiling in DB1 is also a good choice.

upvoted 0 times

...

Justa

5 months ago

You need to enable Last_Query_Plan_Stats in DB1 for sure.

upvoted 0 times

...

Arminda

5 months 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.

upvoted 0 times

...

Marti

5 months 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.

upvoted 0 times

...

Barb

5 months 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.

upvoted 0 times

...

Meaghan

5 months 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.

upvoted 0 times

...

Antonio

5 months 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).

upvoted 0 times

...

Ashlee

5 months ago

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

upvoted 0 times

...

Rose

5 months 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.

upvoted 0 times

...

Erick

5 months 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.

upvoted 0 times

...

Paz

5 months 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?

upvoted 0 times

...