DP-300 Practice Test 2 — 30 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.

Question 11

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

Accepted Answer

sys.dm_exec_query_stats. Sys.dm_exec_query_stats. This DMV returns aggregated performance statistics for cached query plans, including total_worker_time (which represents total CPU time). By filtering on last_execution_time, you can identify queries executed within the last hour. sys.dm_exec_requests (B) shows only currently executing requests, not historical data. sys.dm_db_index_usage_stats (C) tracks index seek/scan operations, not CPU usage. sys.dm_db_resource_stats (D) provides resource consumption at the database level (e.g., DTU, CPU percentage), not per query.

Answer

sys.dm_exec_requests

Answer

sys.dm_db_index_usage_stats

Answer

sys.dm_db_resource_stats

Question 12

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

Accepted Answer

Enable automatic tuning for 'CREATE INDEX' in the Azure portal.. Azure SQL Database's automatic tuning can automatically implement index recommendations when 'CREATE INDEX' is enabled. Option A is incorrect because 'FORCE LAST GOOD PLAN' is for plan correction, not index creation. Option B is incorrect because Query Store is a monitoring tool, not an automatic tuning feature. Option C is incorrect because Azure Advisor only provides recommendations, not automatic implementation.

Answer

Enable automatic tuning for 'FORCE LAST GOOD PLAN' in the Azure portal.

Answer

Set up Query Store to capture the recommended index execution.

Answer

Configure Azure Advisor to email you the recommendation.

Question 13

Refer to the exhibit. You run the above PowerShell command to set the Transparent Data Encryption (TDE) protector for an Azure SQL Database server. What is the result?

Accepted Answer

The server’s TDE protector is changed to a customer-managed key from Azure Key Vault.. The command `Set-AzSqlServerTransparentDataEncryptionProtector` configures the TDE protector at the server level. By specifying the `-Type AzureKeyVault` and `-KeyId` parameters, it sets the server's TDE protector to a customer-managed key stored in Azure Key Vault. Therefore, Option D is correct. Option A is incorrect because the command does not fail due to permissions; it succeeds if permission is granted. Option B is incorrect because TDE remains enabled; only the protector key is changed. Option C is incorrect because the command updates the server's TDE protector, not a specific database.

Answer

The command fails because the service principal does not have permissions to the key vault.

Answer

Transparent Data Encryption is disabled.

Answer

The TDE protector for the database "mydb" is updated.

Question 14

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

Accepted Answer

Query sys.dm_exec_query_stats. sys.dm_exec_query_stats returns aggregate performance statistics for cached query plans, including CPU time, logical reads, and duration, making it ideal for identifying queries that consume the most resources in Azure SQL Database. Option A (sys.dm_exec_requests) shows only currently executing requests, not historical data. Option B (sys.dm_os_wait_stats) provides server-level wait statistics, not per-query resource usage. Option D (sys.dm_db_resource_stats) shows database-level DTU consumption per minute, not per-query details.

Answer

Query sys.dm_exec_requests

Answer

Query sys.dm_os_wait_stats

Answer

Query sys.dm_db_resource_stats

Question 15

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

Accepted Answer

The diagnostic settings are not configured to send QueryStoreRuntimeStatistics to Log Analytics.. Query Store runtime statistics are sent to Azure Diagnostics only if diagnostic settings are configured to collect that category. Option A is wrong because Query Store is enabled. Option C is wrong because the query is correct. Option D is wrong because the KQL query syntax does not require 'project' before 'summarize'; placement does not affect results.

Answer

Query Store is not enabled on the databases.

Answer

The time range in the query is too narrow and excludes the last 24 hours.

Answer

The query syntax is incorrect and needs to use 'project' before 'summarize'.

Question 16

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

Accepted Answer

Azure Monitor. Azure Monitor alerts can be configured to trigger based on metrics like storage percent, enabling automatic notification when an Azure SQL Database reaches 80% storage usage. Option A is incorrect because Azure Automation is used for runbooks and process automation, not for alerting. Option B is incorrect because Microsoft Sentinel is a SIEM solution for security analytics, not for simple metric alerts. Option C is incorrect because Azure Logic Apps can be triggered by alerts, but the alert itself must be created in Azure Monitor.

Answer

Azure Automation

Answer

Microsoft Sentinel

Answer

Azure Logic Apps

Question 17

Which THREE components are part of an Azure SQL Managed Instance automated maintenance window configuration? (Choose three.)

Accepted Answer

Start time (e.g., 2:00 AM local time). Azure SQL Managed Instance allows you to configure a maintenance window including the day of week (e.g., Sunday), start time (e.g., 2:00 AM local time), and a notification email for upcoming maintenance. Option C is incorrect because maintenance windows are instance-level, not per database. Option D is incorrect because it applies to Azure SQL Database, not Managed Instance.

Answer

Database-level maintenance schedule

Answer

Maximum number of concurrent databases

Question 18

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

Accepted Answer

Azure Monitor metric alerts. Azure Monitor metric alerts can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A (Azure SQL Analytics) is for analyzing logs, not triggering actions. Option B (Azure Log Analytics) is for log analytics, not for direct metric alerts triggering runbooks. Option C (Kusto Query Language) is a query language, not a service.

Answer

Azure SQL Analytics

Answer

Azure Log Analytics

Answer

Kusto Query Language (KQL)

Question 19

You are automating the scaling of an Azure SQL Database based on workload patterns. The database uses the DTU purchasing model. You need to ensure that scaling actions do not cause connection drops. Which scaling method should you use?

Accepted Answer

Use the Azure portal or PowerShell to change the service tier within the same DTU tier (e.g., S2 to S3) to avoid downtime.. Azure SQL Database supports online scaling without connection drops when scaling within the same DTU tier (e.g., from S2 to S3). This is because the database remains online and the operation is a metadata change. Option D is correct. Option A is incorrect: creating a new database and using point-in-time restore does not scale the existing database and causes downtime during restore. Option B is incorrect: ALTER DATABASE with ONLINE option is for file operations, not for scaling service tiers. Option C is incorrect: Elastic Database Jobs are for scheduling scripts across databases, not for scaling operations.

Answer

Create a new database at the target size and use point-in-time restore to copy data.

Answer

Scale using the ALTER DATABASE T-SQL command with the ONLINE option.

Answer

Use Elastic Database Jobs to schedule scaling during maintenance windows.

Question 20

Your company has an Azure SQL Database that stores financial data. You need to implement auditing to track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL). Which audit action group should you configure?

Accepted Answer

SCHEMA_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_CHANGE_GROUP. To track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL) in Azure SQL Database, you need to audit both data changes and schema changes. The SCHEMA_OBJECT_CHANGE_GROUP captures DDL changes like CREATE, ALTER, DROP on schema objects, while DATABASE_OBJECT_CHANGE_GROUP captures DML changes on database objects (tables, views). Together, they cover the required tracking. Option A is correct because these two action groups specifically target the stated requirements.

Answer

DATABASE_OBJECT_PERMISSION_CHANGE_GROUP and SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP

Answer

DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP and SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP

Answer

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP