DP-300 Practice Exam 3 — 20 Questions

Question 1

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

Accepted Answer

Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.. Option B is correct because REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore indexes.

Answer

Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

Answer

Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

Answer

Rebuild the entire database by creating a new database and copying data.

Question 2

You are designing a disaster recovery strategy for an Azure SQL Database that requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 1 hour. Which option should you choose?

Accepted Answer

Use active geo-replication and plan manual failover.. Active geo-replication provides a continuous replication stream with an RPO of less than 5 seconds and supports manual failover, which can achieve an RTO of less than 1 hour when the failover is initiated promptly. This option meets the strict RPO and RTO requirements by maintaining a readable secondary in a different Azure region that is kept nearly synchronized with the primary.

Answer

Use auto-failover groups with read-write failover policy.

Answer

Use geo-restore from geo-redundant backups.

Answer

Use long-term retention (LTR) backups with daily frequency.

Question 3

You are configuring automated backup retention for Azure SQL Managed Instance. The compliance policy requires that you be able to restore a database to any point within the last 90 days, and that you keep backups for a minimum of 7 years for auditing purposes. Which backup retention policy should you configure?

Accepted Answer

Set PITR retention to 35 days and configure a long-term retention (LTR) policy to keep weekly backups for 7 years.. Azure SQL Managed Instance's point-in-time restore (PITR) retention is capped at 35 days, so to meet the 90-day restore requirement, you must combine a 35-day PITR policy with a long-term retention (LTR) policy that keeps weekly full backups for 7 years. LTR allows you to restore to any point within the LTR window by using full, differential, and log backups, satisfying both the 90-day point-in-time restore and the 7-year auditing compliance.

Answer

Set point-in-time restore (PITR) retention to 90 days.

Answer

Set PITR retention to 90 days and configure geo-replication to achieve the 7-year retention.

Answer

Configure geo-redundant backup storage with a retention of 90 days.

Question 4

A company uses Azure SQL Database Hyperscale tier for a large database. They need to perform a disaster recovery drill by failing over to a secondary region with minimal data loss. The secondary is in a paired region and is readable. Which approach should they use?

Accepted Answer

Use an Auto-Failover Group with the secondary in the paired region. Auto-Failover Groups with Azure SQL Database Hyperscale provide automated, orchestrated failover to a secondary region with minimal data loss by using synchronous replication for the log service. This meets the requirement for a disaster recovery drill with a readable secondary and minimal data loss, as the secondary is kept in sync and can be failed over manually or automatically.

Answer

Restore a geo-redundant backup to the secondary region

Answer

Configure Active Geo-Replication to the secondary region

Answer

Create a named replica in the secondary region and fail over manually

Question 5

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Accepted Answer

Internal Load Balancer. A is correct because an Internal Load Balancer is required to route traffic to the primary replica in a SQL Server Always On Availability Group (AG) deployed on Azure Virtual Machines. The listener uses the ILB's frontend IP and health probe to direct client connections to the current primary node, as the Windows Server Failover Cluster (WSFC) does not support the cluster IP address in Azure without a load balancer.

Answer

Azure Files share for witness

Answer

Azure SQL Database

Answer

VPN gateway between regions

Question 6

You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?

Accepted Answer

Network connectivity between the replicas is blocked on port 5022. The PENDING_FAILOVER state on the primary replica indicates that a failover was initiated but could not complete because the secondary replica is DISCONNECTED. Error 35202 specifically indicates that a connection attempt to the secondary replica failed, which in an Always On Availability Group (AG) is typically due to a network connectivity issue on the dedicated endpoint port (default 5022). Since the secondary is unreachable, the failover cannot finalize, leaving the primary in a pending state.

Answer

The availability group listener is not configured correctly

Answer

The primary instance encountered a critical error and failed over automatically

Answer

The cluster has lost quorum

Question 7

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

Accepted Answer

Hyperscale tier with 4 vCores. The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Answer

Business Critical tier with 8 vCores

Answer

Serverless tier with auto-pause

Answer

General Purpose tier with 2 vCores

Question 8

A company has an Azure SQL Managed Instance that is experiencing high CPU usage. The DBA observes that a specific query is causing high compile time due to parameter sniffing. The query is executed frequently with varying parameter values. Which approach should the DBA use to reduce CPU usage without changing the T-SQL code?

Accepted Answer

Enable forced parameterization for the database. Forced parameterization (Option D) converts literal values in the query to parameters, allowing SQL Server to reuse cached execution plans across different parameter values. This reduces CPU usage by eliminating frequent recompilations caused by parameter sniffing, without requiring any changes to the T-SQL code itself.

Answer

Create a plan guide to force a specific plan

Answer

Use optimize for ad hoc workloads

Answer

Add query store and force the last good plan

Question 9

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Accepted Answer

Cross-database queries (three- and four-part names). Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Answer

Transparent Data Encryption (TDE)

Answer

Elastic pools

Question 10

A DBA is migrating a large on-premises database to Azure SQL Database using the Data Migration Assistant (DMA). The migration fails with an error indicating that the source database contains cross-database queries. What is the best remediation?

Accepted Answer

Refactor the application to eliminate cross-database queries or use elastic query. Azure SQL Database does not support cross-database queries natively. The Data Migration Assistant (DMA) blocks migrations that rely on such queries because the PaaS service lacks the necessary server-level context. The correct remediation is to refactor the application to eliminate cross-database dependencies or use elastic query, which provides a read-only, schema-bound mechanism to query remote databases via external data sources and external tables.

Answer

Use elastic database query to reference external tables

Answer

Create a linked server in Azure SQL Database

Answer

Migrate to Azure SQL Managed Instance instead

Question 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 20

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