DP-300 Practice Test 4 — 10 Questions

Question 1

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Accepted Answer

Target groups. Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Answer

Azure Automation account

Answer

SQL Server Agent

Question 2

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Accepted Answer

Update statistics on the table to ensure the optimizer has current information.. Updating statistics provides the query optimizer with current data distribution information, which can lead to a more efficient query plan, potentially avoiding the clustered index scan. In Azure SQL Database, stale statistics are a common cause of suboptimal plans, and updating them is a low-cost, non-disruptive first step before considering index changes.

Answer

Rebuild the clustered index to reduce fragmentation.

Answer

Force a different query plan using Query Store hints.

Answer

Increase the DTU service tier of the database.

Question 3

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

Accepted Answer

Insufficient IOPS or throughput at the database level. PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O waits. Option C is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits. Option D is incorrect because blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Answer

Out-of-date statistics

Answer

Missing indexes

Answer

Blocking from long-running transactions

Question 4

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because Azure Advisor provides recommendations but not real-time alerts. Option C is wrong because Azure SQL Insights is for visualization, not alerting. Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Answer

Azure Advisor

Answer

Azure SQL Insights (preview)

Answer

Log Analytics workspace

Question 5

You are configuring Microsoft Defender for SQL for Azure SQL Database. You need to ensure that alerts are sent to the security operations team via email and also integrated with Microsoft Sentinel. What should you configure?

Accepted Answer

In Microsoft Sentinel, connect the Azure SQL Database data source using the built-in connector.. Microsoft Sentinel provides a built-in connector for Azure SQL Database that ingests security alerts from Microsoft Defender for SQL, satisfying the Sentinel integration requirement. The email notification requirement is addressed separately by configuring alert rules within Defender for SQL to send emails to the security operations team. Option A enables the Sentinel integration, while email notifications are a complementary configuration within Defender for SQL, not part of Option A itself.

Answer

Use Azure Logic Apps to forward Defender for SQL alerts to Sentinel.

Answer

Configure a diagnostic setting on the SQL server to stream logs to a Log Analytics workspace used by Sentinel.

Answer

Configure the alert rule in Defender for SQL to send email to the security team.

Question 6

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

Accepted Answer

Query Store. Query Store is built-in and designed for low overhead query performance monitoring. Option B is wrong because Azure Monitor metrics provide resource-level metrics, not query-level details. Option C is wrong because Extended Events can have higher overhead and is more for custom event collection. Option D is wrong because SQL Server Profiler is deprecated and has high overhead.

Answer

Azure Monitor metrics

Answer

Extended Events

Answer

SQL Server Profiler

Question 7

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics in Azure Monitor provides historical storage metrics. Option A is wrong because Intelligent Insights is for proactive diagnostics, not storage monitoring. Option C is wrong because Query Store focuses on query performance. Option D is wrong because SSMS does not provide historical monitoring.

Answer

Intelligent Insights

Answer

Query Store

Answer

SQL Server Management Studio (SSMS)

Question 8

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

Accepted Answer

Use columnstore indexes on large tables. Options A and D are correct. Columnstore indexes (A) improve analytical query performance by reducing I/O and using batch processing. Query Store (D) helps identify regressions by tracking execution plans and performance metrics. Option B is incorrect; setting database compatibility level to 150 may enable new features but is not a direct query optimization method. Option C is incorrect; increasing storage size does not improve query performance. Option E is incorrect; Transparent Data Encryption (TDE) secures data but does not enhance performance.

Answer

Set database compatibility level to 150

Answer

Increase the maximum storage size

Answer

Enable Transparent Data Encryption (TDE)

Question 9

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. (sys.dm_exec_query_stats) is correct because this DMV returns aggregated performance statistics for cached query plans, including total CPU time. It can be filtered to look at queries over the last hour. Option A (sys.dm_exec_sessions) shows current session-level information, not query-level CPU. Option B (sys.dm_exec_query_plan) returns the actual execution plan, not performance statistics. Option D (sys.dm_exec_requests) shows currently executing requests, not historical CPU consumption.

Answer

sys.dm_exec_sessions

Answer

sys.dm_exec_query_plan

Answer

sys.dm_exec_requests

Question 10

Which THREE factors should you consider when choosing between vCore and DTU purchase models for Azure SQL Database performance optimization?

Accepted Answer

DTU is simpler for customers who want a bundled metric.. The DTU (Database Transaction Unit) model bundles compute, storage, and I/O into a single, simple metric, making it easier for customers who want a straightforward, pre-configured performance tier without needing to manage individual resources. This contrasts with the vCore model, which requires separate configuration of vCores, memory, and storage, offering more granular control but greater complexity.

Answer

Only vCore supports Azure Hybrid Benefit.

Answer

Only DTU supports elastic pools.