You are reviewing an execution plan for a query that uses a parameterized predicate. The plan shows an index seek, but the actual number of rows is much higher than estimated. The query runs slowly for some parameter values but not others. What is the most likely cause and what should you do?

The statistics are outdated; update statistics on the indexed column.

The query is using a hash join; force a nested loop join.

The index is fragmented; rebuild the index to improve estimates.

The parameter sniffing issue is causing a suboptimal plan; use the RECOMPILE hint to generate a new plan each time.

Where can you view Intelligent Insights reports for an Azure SQL Database?

SQL Server Management Studio's Object Explorer

Azure portal under the database's Intelligent Insights page

You are tuning a query that uses a scalar UDF in the WHERE clause. The execution plan shows that the UDF is executed for each row, causing high CPU and long duration. You need to improve performance while preserving the logic. What should you recommend?

Rewrite the UDF as an inline table-valued function and use CROSS APPLY.

Add an index on the column used in the UDF.

Increase the database's MAXDOP to parallelize the UDF execution.

Use a scalar UDF with SCHEMABINDING to improve performance.

Microsoft Certified:Azure Database Administrator Associate

Domain 3Objective 2

Monitor and Optimize Query Performance DP-300 Practice Questions (Page 4)

Part of the Monitor, configure, and optimize database resources domain, which accounts for 20–25% of the DP-300 exam. Microsoft does not publish an official question count, but from its 100-minute exam (~40–65 total, ~8–16 in this domain), expect 3–5 from this objective — we provide 42 practice questions to prepare you well beyond it. (estimate)

42questions here

9free pages

10concepts

20–25%of the exam

Questions 16–20

16expert · hard
You are reviewing an execution plan for a query that uses a parameterized predicate. The plan shows an index seek, but the actual number of rows is much higher than estimated. The query runs slowly for some parameter values but not others. What is the most likely cause and what should you do?
Select an answer first
17foundation · easy
Where can you view Intelligent Insights reports for an Azure SQL Database?
Select an answer first
18foundation · easy
In Query Store, which view contains the execution statistics for each query, such as duration and CPU time?
Select an answer first
19foundation · easy
Which DMV provides aggregated performance statistics for queries that have completed, such as total CPU time and total duration?
Select an answer first
20expert · hard
You are tuning a query that uses a scalar UDF in the WHERE clause. The execution plan shows that the UDF is executed for each row, causing high CPU and long duration. You need to improve performance while preserving the logic. What should you recommend?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-300

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Microsoft. “DP-300” is a trademark of its owner, used for identification only.

Monitor and Optimize Query Performance DP-300 Practice Questions (Page 4)

Questions 16–20

You are reviewing an execution plan for a query that uses a parameterized predicate. The plan shows an index seek, but the actual number of rows is much higher than estimated. The query runs slowly for some parameter values but not others. What is the most likely cause and what should you do?

Where can you view Intelligent Insights reports for an Azure SQL Database?

In Query Store, which view contains the execution statistics for each query, such as duration and CPU time?

Which DMV provides aggregated performance statistics for queries that have completed, such as total CPU time and total duration?

You are tuning a query that uses a scalar UDF in the WHERE clause. The execution plan shows that the UDF is executed for each row, causing high CPU and long duration. You need to improve performance while preserving the logic. What should you recommend?

Keep studying — Monitor, configure, and optimize database resources