DP-300 Hard Questions — 25 Difficult Questions

Question 1

A company has an Azure SQL Database with a large table that is frequently updated. They notice performance degradation due to index fragmentation. Which maintenance strategy should you recommend to minimize impact on availability and performance?

Accepted Answer

Use online index rebuild with resumable operation.. Option B is correct because online index rebuild with resumable operation allows the index to be rebuilt while the table remains available for reads and writes, minimizing downtime. The resumable feature lets you pause and resume the rebuild if it is interrupted, which is critical for large tables that are frequently updated. This approach reduces performance impact compared to offline rebuilds and avoids blocking issues associated with other online methods.

Answer

Schedule an offline index rebuild during off-peak hours.

Answer

Disable and rebuild indexes manually during maintenance window.

Answer

Use online index reorganize with low priority locks.

Question 2

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Accepted Answer

Create appropriate indexes on the columns used in joins and filters.. An Index Spool (Lazy Spool) operator in an execution plan indicates that the query engine is creating a temporary index on the fly to support join or filter operations, which is a sign of missing or inadequate permanent indexes. Creating appropriate indexes on the columns used in joins and filters (Option C) eliminates the need for the spool, reducing I/O and improving performance. Updating statistics (Option E) ensures the query optimizer has accurate distribution information to generate efficient plans, which can also help avoid spool operations.

Answer

Disable parallelism for the query using the MAXDOP 1 hint.

Answer

Increase the DTU or vCore count of the database.

Answer

Rewrite the query using table hints to force a specific join order.

Question 3

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

Accepted Answer

Create an Always On Availability Group with an availability group listener. An Always On Availability Group (AG) with a listener provides high availability with automatic failover at the database level without requiring shared storage. This solution meets the requirement for automatic failover and minimizes downtime during planned maintenance by allowing manual failover to a synchronized secondary replica with minimal disruption.

Answer

Configure Log Shipping to a secondary VM

Answer

Deploy a Failover Cluster Instance using Azure Shared Disks

Answer

Use Database Mirroring with automatic failover

Question 4

You are designing an automated backup strategy for Azure SQL Database. Which TWO actions satisfy a requirement to maintain daily backups for 30 days and ensure recoverability if the Azure region becomes unavailable?

Accepted Answer

Enable geo-redundant backup storage for the server.. Option A is correct because enabling geo-redundant backup storage (RA-GRS) for Azure SQL Database ensures that automated backups are replicated to a paired Azure region. This satisfies the requirement for recoverability if the primary region becomes unavailable, as backups can be restored from the secondary region. Combined with setting point-in-time restore retention to 30 days (Option C), you maintain daily backups for the full 30-day retention period, meeting both requirements.

Answer

Deploy a read-scale replica in a different Azure region.

Answer

Set up a long-term retention policy with a retention of 30 days.

Answer

Configure active geo-replication to a secondary region.

Question 5

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.. Option B is correct because 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 6

You are designing a database solution for an e-commerce application that requires high read throughput with sub-5 ms latency. The application runs on Azure VMs. You need to choose between Azure SQL Database and Azure SQL Managed Instance. Which factor most strongly supports choosing Azure SQL Database over SQL Managed Instance?

Accepted Answer

Azure SQL Database supports Hyperscale tier with sub-5 ms read latency on local SSDs.. Option B is correct because the Hyperscale tier of Azure SQL Database uses a distributed architecture with local SSD-based buffer pool extensions on compute nodes, enabling sub-5 ms read latency for data cached locally. This directly meets the e-commerce application's requirement for high read throughput with low latency, whereas Azure SQL Managed Instance relies on remote premium page blobs for storage, which introduces higher latency for reads that are not cache-resident.

Answer

Azure SQL Database offers a wider range of built-in instance-level features.

Answer

Azure SQL Database allows databases up to 100 TB in size.

Answer

Azure SQL Database supports active geo-replication with read-scale secondaries.

Question 7

You are automating index maintenance for an Azure SQL Database. Which THREE of the following should you consider to minimize performance impact?

Accepted Answer

Use the ONLINE = ON option when rebuilding indexes.. Option A is correct because using ONLINE = ON when rebuilding indexes in Azure SQL Database allows concurrent user access to the underlying table during the index rebuild operation. This minimizes blocking and performance impact on production workloads, as the index rebuild is performed as a series of short-term locks rather than a single long-term schema modification lock.

Answer

Always use SORT_IN_TEMPDB = ON to reduce tempdb contention.

Answer

Set MAXDOP to 0 to use all CPUs for faster execution.

Question 8

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

Accepted Answer

Configure SQL Server Managed Backup to Microsoft Azure.. SQL Server Managed Backup to Microsoft Azure (also known as Managed Backup) is the correct choice because it provides automated, policy-based backup management with minimal administrative effort. It natively supports point-in-time restore by automatically scheduling full, differential, and transaction log backups to Azure Blob Storage, and it handles backup retention and recovery point management without requiring custom scripts or additional infrastructure.

Answer

Install Azure Backup Server on-premises and configure backup of SQL Server databases.

Answer

Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

Answer

Use Azure Data Factory to copy database backups to Blob Storage.

Question 9

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 10

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 11

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 12

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 13

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 14

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 15

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 16

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 17

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 18

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 19

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 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