DP-300 Practice Questions 2 — 50 Questions

Question 1

A company plans to deploy Azure SQL Database in a new region. They require that the database be accessible only from a specific set of Azure virtual machines in the same region. What should the DBA configure?

Accepted Answer

Configure a VNet service endpoint and a firewall rule for the VNet subnet. Option B is correct because a VNet service endpoint extends your virtual network private address space and the identity of your VNet to the Azure SQL Database service over a direct connection. By combining a service endpoint on the subnet with a firewall rule that allows traffic only from that specific VNet/subnet, you restrict access exclusively to Azure VMs in that subnet, meeting the requirement without exposing the database to the public internet.

Answer

Use a private endpoint only

Answer

Configure a point-to-site VPN

Answer

Set the firewall to allow Azure services and resources to access the server

Question 2

Refer to the exhibit. An Azure SQL Database administrator sees the error in the SQL Server error log. The database is in the General Purpose tier with 100 GB of storage. Which action should the DBA take to resolve the issue?

Accepted Answer

Increase the max size of the database. The error indicates the database has reached its maximum size limit of 100 GB. In Azure SQL Database General Purpose tier, the max size is set at the database level, not the log file level. Increasing the max size of the database (Option D) directly resolves the 'could not allocate space' error by allowing more data to be stored, without requiring a tier change or manual log file manipulation.

Answer

Shrink the database

Answer

Scale up to a higher service objective (e.g., from GP to BC)

Answer

Increase the log file size

Question 3

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Accepted Answer

I/O subsystem bottleneck. The high PAGEIOLATCH_SH wait time indicates I/O subsystem pressure, likely due to insufficient IOPS or throughput on the database storage.

Answer

Blocking due to lock contention

Answer

CPU pressure from high-complexity queries

Answer

Insufficient memory allocation for the database

Question 4

Refer to the exhibit. An Azure SQL Database in the Standard tier (S2: 50 DTU) is consistently showing high DTU consumption. Which action would most effectively reduce DTU usage?

Accepted Answer

Create an index on the tables accessed by Query 1234. Query 1234 has high logical reads, indicating inefficient queries causing high DTU. Optimizing it would reduce resource consumption.

Answer

Increase the log_write_percent by adjusting transaction log settings

Answer

Scale up to a higher service tier (e.g., S3)

Answer

Rebuild all indexes in the database

Question 5

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

Accepted Answer

The transaction log is not being truncated, possibly due to an active transaction or replication. The log space is nearly full (95% used), and the log write rate is high, indicating transactions are not being committed or log is not being truncated due to long-running transactions or replication.

Answer

The database storage is almost full, preventing log growth

Answer

The log rate limit is being throttled due to high log IO percentage

Answer

The database should be scaled to BusinessCritical tier for faster log writes

Question 6

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

Accepted Answer

Monitor the query performance to confirm the forced plan resolves the regression. The recommendation is active and the query regressed, so forcing the last good plan is appropriate. The DBA should verify the plan change improved performance.

Answer

Immediately implement the DROP_INDEX recommendation to reduce overhead

Answer

Create the recommended index to improve performance

Answer

Revert the plan force because it is causing regression

Question 7

Refer to the exhibit. An administrator wants to restore the primary database SalesDB to a point in time 2025-03-15T09:00:00Z. What is the impact on the geo-replication?

Accepted Answer

The geo-replication will be terminated, and the secondary will become a standalone database.. Restoring the primary database to an earlier point in time will break the geo-replication link. The secondary database will become a regular database, and replication must be reconfigured after the restore. The restore is possible because the requested time is within the restore window.

Answer

The geo-replication will continue seamlessly after the restore.

Answer

The restore will fail because the database is in a geo-replication relationship.

Answer

The secondary database will automatically be restored to the same point in time.

Question 8

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

Accepted Answer

Identify and create missing indexes.. High CPU usage in an S3 Azure SQL Database often stems from inefficient query plans caused by missing indexes. Creating appropriate indexes reduces the number of rows scanned and the CPU cycles needed for operations like key lookups and sorting, directly lowering CPU consumption without changing the service tier.

Answer

Increase the maximum number of concurrent workers.

Answer

Reduce MAXDOP to 1.

Answer

Increase MAXDOP to 8.

Question 9

Which TWO actions can help reduce `PAGEIOLATCH_UP` waits on an Azure SQL Database?

Accepted Answer

Add a nonclustered index to reduce update I/O.. A is correct because adding a nonclustered index can reduce the number of pages that need to be updated during write operations, thereby decreasing the I/O load that causes `PAGEIOLATCH_UP` waits. These waits occur when a task is waiting for a page I/O request to complete while holding an update latch. By optimizing the index structure, fewer pages are modified, reducing the I/O contention.

Answer

Reduce MAXDOP to 1.

Answer

Increase the buffer pool size by adding more memory.

Question 10

You manage an Azure SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled for the instance. Which PowerShell cmdlet should you run?

Accepted Answer

Set-AzSqlInstanceDatabase with -AutoTuningProperties. The correct cmdlet is Set-AzSqlInstanceDatabase because Azure SQL Managed Instance uses instance-level databases, and automatic tuning properties are configured at the database level within the instance. The -AutoTuningProperties parameter directly enables or configures automatic tuning recommendations for the specified database in the managed instance.

Answer

Set-AzSqlDatabase with -AutoTuningProperties

Answer

Set-AzSqlInstance with -DtcEnabled

Answer

Set-AzSqlServer with -AdministratorLogin

Question 11

You have an Azure SQL Database that uses the General Purpose service tier. You notice that the log write throughput is consistently near the limit. What should you do to improve log write performance?

Accepted Answer

Migrate to the Business Critical service tier.. The General Purpose service tier in Azure SQL Database has a maximum log write throughput of 1.5 MB/s for the most common configurations. The Business Critical tier uses local SSD storage and a higher log I/O limit (up to 100 MB/s), which directly addresses log write throughput bottlenecks. Migrating to Business Critical is the correct action because it provides significantly higher log write throughput and lower latency for transaction log writes.

Answer

Enable accelerated database recovery.

Answer

Migrate to the Hyperscale service tier.

Answer

Increase the DTU purchase model to a higher tier.

Question 12

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

Accepted Answer

Missing nonclustered index; create an index on the predicates.. Query Performance Insight shows a query with high duration and CPU usage, and the query plan reveals a clustered index scan. A clustered index scan reads all rows in the table, which is inefficient when only a subset of rows is needed. The most likely cause is a missing nonclustered index on the columns used in the WHERE clause (predicates), which would allow a seek operation instead of a full scan, reducing both CPU and duration.

Answer

Fragmented clustered index; rebuild the clustered index.

Answer

Insufficient memory; increase the service tier.

Answer

Parameter sniffing; add OPTION (RECOMPILE).

Question 13

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

Accepted Answer

Automatic tuning. Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Answer

Azure Advisor

Answer

Intelligent Insights

Answer

Query Store

Question 14

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

Accepted Answer

Query Store. Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Answer

Data classification

Answer

Server-level audit

Answer

Diagnostic settings for SQLInsights

Question 15

Which THREE metrics should you monitor to detect a memory pressure issue in Azure SQL Database?

Accepted Answer

avg_page_life_expectancy. B (avg_page_life_expectancy) is correct because it measures how long (in seconds) a data page stays in the buffer pool before being evicted. A low value (typically below 300 seconds) indicates that pages are being flushed quickly due to memory pressure, forcing more physical I/O. This is a direct indicator of insufficient memory for the buffer cache.

Answer

avg_io_stall_ms

Answer

log_bytes_used

Question 16

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

Accepted Answer

I/O subsystem bottleneck. The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Answer

Blocking

Answer

CPU bottleneck

Answer

Memory pressure

Question 17

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that read-only workloads are offloaded to a readable secondary. Which configuration should you set?

Accepted Answer

Use ApplicationIntent=ReadOnly in the connection string.. Option D is correct because setting `ApplicationIntent=ReadOnly` in the connection string directs read-only workloads to a readable secondary replica in Azure SQL Database Hyperscale. This offloads read traffic from the primary, improving performance for write-heavy operations. The Hyperscale tier supports this feature without requiring a failover group or explicit read-scale configuration.

Answer

Set ReadOnlyRouting=1 on the database.

Answer

Add the database to a failover group.

Answer

Set ReadScale to 1 on the database.

Question 18

Which THREE actions can be performed by using Elastic Database Jobs in Azure SQL Database? (Choose three.)

Accepted Answer

Run a T-SQL script to update statistics across multiple databases.. Elastic Database Jobs in Azure SQL Database are designed to automate administrative tasks across multiple databases. Running a T-SQL script to update statistics is a common maintenance operation that can be executed in parallel across a target group of databases, making option A correct.

Answer

Create a new Azure SQL Database.

Answer

Change the service tier objective (SLO) of a database.

Question 19

You are reviewing the encryption protector for an Azure SQL Server as shown in the exhibit. The server hosts multiple databases. You need to ensure that the databases are encrypted using the customer-managed key from Azure Key Vault. However, you find that the databases are not using this key. What is the most likely reason?

Accepted Answer

The databases still have the service-managed key as protector and need to be updated.. The exhibit shows that the server's encryption protector is set to a customer-managed key from Azure Key Vault, but this setting only applies to newly created databases. Existing databases retain their original encryption protector, which is typically the service-managed key. To enforce the customer-managed key on existing databases, you must manually update each database's encryption protector using the ALTER DATABASE ENCRYPTION KEY statement or through the Azure portal. Option C correctly identifies that the databases still have the service-managed key as protector and need to be updated.

Answer

The key version '123456' is incorrect.

Answer

The key vault is in a different subscription and not accessible.

Answer

The 'serverKeyName' property is not set correctly.

Question 20

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Accepted Answer

Use in-memory OLTP to reduce log writes.. High WRITELOG waits indicate that the transaction log is a bottleneck, often due to excessive log I/O. In-memory OLTP reduces log writes by logging only the delta changes for memory-optimized tables, rather than full row versions, which directly alleviates log pressure. This makes option B correct.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Query Store.

Answer

Increase the frequency of database backups.