DP-300 Practice Test 17 — 15 Questions

Question 1

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 2

Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?

Accepted Answer

Enable adaptive joins. Adaptive joins (A) are an Intelligent Query Processing feature that lets the optimizer defer the choice between a hash join and a nested loops join until runtime, switching to the better plan based on actual row counts and thereby improving performance for queries with inaccurate cardinality estimates. Interleaved execution for MSTVFs (B) is also an IQP feature that lets multi-statement table-valued functions be executed in an interleaved manner so the optimizer can use actual row counts from the function's first execution to produce a better overall plan. Approximate count distinct (E) is an IQP capability (APPROX_COUNT_DISTINCT) that returns statistically accurate distinct counts with much less CPU and memory than exact COUNT(DISTINCT), speeding up aggregation-heavy queries. Query Store (C) is a monitoring and plan-capture feature, not an IQP query-optimization action, and columnstore indexes (D) are a physical data-access/columnar storage technology rather than an Intelligent Query Processing optimization, so neither belongs among the three IQP actions.

Answer

Enable Query Store

Answer

Enable columnstore indexes

Question 3

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

Accepted Answer

NVARCHAR. B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Answer

TEXT

Answer

XML

Answer

VARCHAR

Question 4

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

Accepted Answer

Use an elastic pool and add databases as needed. Elastic pools are designed for multi-tenant SaaS scenarios where each tenant has its own database but usage patterns are unpredictable. They provide performance isolation through per-database resource limits (e.g., min/max DTUs or vCores) while sharing a fixed pool of resources, which minimizes cost by allowing idle databases to borrow from others. This matches the requirement of fluctuating tenant counts and cost efficiency.

Answer

Deploy each tenant's database as a single database with reserved capacity

Answer

Use a single Hyperscale database with schema per tenant

Answer

Use a single Azure SQL Managed Instance with multiple databases

Question 5

You are responsible for automating index maintenance in Azure SQL Database. You need to ensure that index rebuilds and reorganizations are performed only when fragmentation exceeds 30% and 10%, respectively, and that the job runs weekly. Which approach should you use?

Accepted Answer

Use Elastic Database Jobs to run a script that rebuilds/reorganizes indexes.. Elastic Database Jobs are the native Azure SQL Database mechanism for scheduling and executing T-SQL across one or more databases without requiring a separate VM or SQL Server Agent. The job can run a custom T-SQL script that queries sys.dm_db_index_physical_stats to evaluate fragmentation and conditionally issue ALTER INDEX ... REBUILD or REORGANIZE based on the 30% and 10% thresholds. This satisfies both the automation and the weekly schedule requirements within the PaaS environment.

Answer

Create a SQL Agent job using T-SQL script with sys.dm_db_index_physical_stats.

Answer

Schedule a maintenance task using SQL Server Agent in a VM running SQL Server.

Answer

Enable automatic index tuning in Azure SQL Database with the desired fragmentation thresholds.

Question 6

You have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

Accepted Answer

Dynamic Data Masking with a masking function configured on the column.. Dynamic Data Masking (DDM) is the correct feature because it masks sensitive data in query results for non-privileged users without altering the actual data at rest. You configure a masking rule (e.g., default(), email(), partial()) on the specific column, and users without UNMASK permission see masked values while admins see the real data. This matches the requirement to 'automate masking of a specific column for non-admin users.'

Answer

Always Encrypted with a column encryption key.

Answer

Row-Level Security to filter rows for non-admin users.

Answer

Azure Policy to enforce masking rules on the database.

Question 7

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

Accepted Answer

Automatic plan correction (FORCE_LAST_GOOD_PLAN). Automatic plan correction (FORCE_LAST_GOOD_PLAN) is the automatic tuning feature that detects plan regressions and forces the last known good plan. It is designed exactly for the scenario where a plan performs worse than a previous plan. Enabling it allows Azure SQL Managed Instance to automatically correct plan choice regressions.

Answer

Automatic index management (DROP INDEX)

Answer

Automatic index management (CREATE INDEX)

Answer

Intelligent Insights

Question 8

Your company has an Azure SQL Database with a failover group configured to a secondary region. The primary region experiences a temporary network issue. The failover group is set to automatic failover with a grace period of 1 hour. What will happen?

Accepted Answer

Automatic failover will start after 1 hour if the primary is still unreachable.. In an Azure SQL failover group with automatic failover and a grace period of 1 hour, the failover is not immediate. Azure waits for the grace period to elapse while the primary remains unreachable before initiating automatic failover to the secondary region. This prevents unnecessary failovers during brief network blips.

Answer

Automatic failover will happen immediately.

Answer

You must manually initiate failover.

Answer

The secondary database will be deleted and re-created.

Question 9

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

Accepted Answer

Scale up to a higher service tier or increase DTUs. When DTU consumption consistently exceeds 90%, the database is resource-constrained, leading to performance degradation. Scaling up to a higher service tier or increasing DTUs directly provides more CPU, memory, and I/O resources, alleviating the bottleneck and reducing DTU utilization percentage. This is the standard corrective action for sustained high DTU usage in Azure SQL Database.

Answer

Scale down to a lower service tier

Answer

Enable geo-replication

Answer

Enable read scale-out

Question 10

You are configuring automatic tuning for an Azure SQL Database. Which THREE recommendations can be applied automatically without manual approval?

Accepted Answer

FORCE LAST GOOD PLAN. Azure SQL Database automatic tuning can automatically apply three types of recommendations: FORCE LAST GOOD PLAN (A), which reverts a query to a previously known good execution plan when a plan regression is detected; CREATE INDEX (C), which adds missing indexes identified by the query optimizer; and DROP INDEX (D), which removes redundant or unused indexes. These three are the built-in automatic tuning actions that Azure SQL Database can execute without manual approval, provided automatic tuning is enabled. Modifying statistics (B) is not an automatic tuning action — statistics updates are handled separately by the database engine's automatic statistics maintenance, not by the automatic tuning feature. Enabling database compression (E) is a manual performance optimization and is not part of the automatic tuning recommendation set.

Answer

Modify statistics

Answer

Enable database compression

Question 11

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

Accepted Answer

FORCE_LAST_GOOD_PLAN. FORCE_LAST_GOOD_PLAN, is the correct automatic tuning option for Azure SQL Database to automatically correct query plan choice regressions. When the database engine detects that a newly compiled query plan performs worse than the previously known good plan, it can automatically force the last known good plan without manual intervention, which is ideal for a heavy OLTP workload where performance stability is critical.

Answer

DROP_INDEX

Answer

CREATE_INDEX

Answer

CORRECT_INDEX

Question 12

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which change should you make?

Accepted Answer

Enable serverless with auto-pause enabled.. Azure SQL Database serverless with auto-pause enabled automatically pauses the database after a period of inactivity and resumes on the next connection, so the database incurs no compute charges outside business hours. This directly matches the 8 AM–6 PM usage pattern and reduces cost without manual intervention.

Answer

Reduce storage to 512 GB.

Answer

Change tier to Hyperscale.

Answer

Reduce capacity to 2 vCores.

Question 13

You are a database administrator for a large e-commerce platform using Azure SQL Database. You notice that a specific query frequently causes high CPU usage during peak hours. The query is a SELECT with multiple JOINs and a WHERE clause on a non-clustered index. You have already updated statistics and rebuilt indexes. What should you do next to optimize performance?

Accepted Answer

Use Query Store to identify and force a better execution plan.. When statistics are updated and indexes rebuilt but a specific query still causes high CPU, the next step is to use Query Store to identify a better execution plan and force it. Query Store captures plan history and runtime stats, so you can pinpoint a previously good plan (e.g., before a plan regression) and force it, immediately stabilizing performance without schema changes.

Answer

Enable automatic tuning to let Azure SQL Database handle the issue.

Answer

Add more indexes on the columns used in JOINs and WHERE clause.

Answer

Create a read replica and offload the query to it.

Question 14

You have an Azure SQL Managed Instance configured with a failover group for disaster recovery. The primary instance is in the East US region and the secondary is in West US. You need to perform a planned failover for maintenance with zero data loss. What is the correct sequence of steps?

Accepted Answer

Use the Azure portal to perform a planned failover for the failover group.. For a planned failover with zero data loss in Azure SQL Managed Instance failover groups, the correct approach is to use the Azure portal (or PowerShell/CLI) to perform a planned failover. This ensures that all pending transactions are synchronized to the secondary before switching roles, preventing data loss.

Answer

Take the primary instance offline, then failover.

Answer

Scale down the primary to reduce cost, then failover.

Answer

Remove the failover group, perform maintenance, then recreate the failover group.

Question 15

Refer to the exhibit. You are reviewing an Azure Resource Manager template for deploying an Azure SQL Database server. The template sets publicNetworkAccess to Disabled, minimalTlsVersion to 1.2, and azureAdOnlyAuthentication to true. However, the deployment fails with an error. What is the most likely cause?

Accepted Answer

When azureAdOnlyAuthentication is true, the administratorLogin and administratorLoginPassword properties must not be specified. When azureAdOnlyAuthentication is set to true in an Azure SQL Database ARM template, the administratorLogin and administratorLoginPassword properties must be omitted because Microsoft Entra ID authentication replaces SQL authentication as the sole identity provider. Including these properties causes a validation conflict, as the deployment expects no SQL admin credentials when Microsoft Entra ID-only authentication is enabled.

Answer

minimalTlsVersion 1.2 is not supported in Azure SQL Database

Answer

publicNetworkAccess Disabled requires a private endpoint to be defined in the same template

Answer

The password does not meet complexity requirements