DP-300 Practice Test 42 — 15 Questions

Question 1

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Accepted Answer

File tables and FILESTREAM.. Azure SQL Managed Instance does not support FILESTREAM or FileTables. These features rely on the NTFS file system for storing BLOB data, which is not available in the managed instance environment. Any migration using FILESTREAM or FileTables must be refactored to use alternative storage, such as Azure Blob Storage with the remote BLOB store (RBS) or separate file storage.

Answer

SQL Server Integration Services (SSIS) packages.

Answer

Use of contained databases.

Question 2

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

Accepted Answer

Include avg_cpu_time to measure CPU usage.. Avg_cpu_time directly measures CPU usage per query, helping correlate high duration with CPU pressure. Option A is incorrect because wait_stats show wait types (e.g., blocking), not CPU consumption. Option B is incorrect because execution count alone does not indicate CPU usage; a query could run frequently but use little CPU. Option C is incorrect because avg_logical_reads measure I/O, not CPU pressure.

Answer

Include wait_stats to see blocking.

Answer

Include count_executions to see frequency.

Answer

Include avg_logical_reads to see I/O consumption.

Question 3

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

Accepted Answer

Query Store with custom retention settings. Query Store captures historical query performance data and allows comparing performance before and after index changes, making it the correct choice for this requirement. SQL Server DMVs (Option B) only provide current state, not historical trends. Azure SQL Analytics solution (Option C) provides aggregated metrics but lacks per-query historical comparison. Intelligent Insights (Option D) offers diagnostic analysis but not detailed historical query data.

Answer

SQL Server DMVs

Answer

Azure SQL Analytics solution in Log Analytics

Answer

Azure SQL Database Intelligent Insights

Question 4

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

Accepted Answer

Increase the yearly retention to P10Y.. The current yearly retention is P3Y (3 years), which is insufficient for the 10-year compliance requirement. Increasing it to P10Y retains yearly backups for 10 years. Option B is incorrect because weekOfYear specifies which week's backup is retained for yearly retention, not the retention period. Option C is incorrect because monthly retention at P120M (120 months = 10 years) would retain 120 monthly backups, but the requirement is for yearly retention, and monthly retention doesn't cover full 10-year compliance on its own. Option D is incorrect because weekly retention at P10W (10 weeks) is far less than 10 years.

Answer

Change the weekOfYear to 10.

Answer

Increase the monthly retention to P120M.

Answer

Increase the weekly retention to P10W.

Question 5

Your organization runs a critical e-commerce application on Azure SQL Database. You notice that during peak hours, query performance degrades significantly. You have enabled Intelligent Insights and Query Store. Which diagnostic metric would you analyze first to identify the root cause of the performance degradation?

Accepted Answer

DTU/CPU consumption. DTU/CPU consumption directly correlates with query performance during peak load. With Intelligent Insights and Query Store enabled, analyzing DTU/CPU consumption helps identify resource contention as the primary cause of degradation. Option A (Query store wait statistics) is useful for detailed analysis but is not the first metric to check. Option B (Deadlock count) is not a typical cause of sustained degradation. Option D (Failed connections) indicates availability issues, not performance.

Answer

Query store wait statistics

Answer

Deadlock count

Answer

Failed connections

Question 6

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

Accepted Answer

Automatic Tuning. Automatic Tuning can automatically identify and fix regressed query plans. Option A is wrong because Query Store captures performance data but does not automatically fix plans. Option C is wrong because Intelligent Insights provides monitoring, not automatic fixing. Option D is wrong because Database Advisor for SQL Database gives recommendations but requires manual action.

Answer

Query Store

Answer

Intelligent Insights

Answer

Database Advisor for SQL Database

Question 7

You are troubleshooting a performance issue in Azure SQL Database. You suspect that parameter sniffing is causing suboptimal query plans. What feature can you use to mitigate this without code changes?

Accepted Answer

Set the database to FORCE_PARAMETERIZATION = ON.. FORCE_PARAMETERIZATION = ON is a database-wide setting that forces parameterization of all ad-hoc queries, helping to mitigate parameter sniffing issues without requiring code changes. Option A (reorganizing indexes) addresses fragmentation, not parameter sniffing. Option B (Query Store plan forcing) is used to pin a specific plan after a regression, not to prevent sniffing proactively. Option C (OPTIMIZE FOR UNKNOWN) is a query hint that requires modifying query code, not a code-less solution.

Answer

Reorganize indexes regularly.

Answer

Use Query Store to force a good plan.

Answer

Add OPTIMIZE FOR UNKNOWN query hint to all queries.

Question 8

You have an Azure SQL Database that is configured with automatic failover groups. During a planned failover, you notice that the failover takes longer than expected. You need to minimize downtime during future planned failovers. What should you do?

Accepted Answer

Pre-seed the secondary replica by initiating a manual sync before the planned failover.. Pre-seeding the secondary replica by initiating a manual sync before the planned failover ensures that the secondary is fully up to date, reducing the synchronization time during the actual failover and thereby minimizing downtime. Option A is incorrect because increasing the service tier of the secondary does not reduce failover time; it only improves performance. Option B is incorrect because removing the failover group would eliminate the failover capability, not improve planned failover speed. Option D is incorrect because a forced failover with data loss is for unplanned scenarios and would cause data loss, which is not recommended for planned failovers.

Answer

Increase the service tier of the secondary to match the primary.

Answer

Remove the failover group and use a different disaster recovery strategy.

Answer

Use a forced failover with data loss to speed up the process.

Question 9

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for a column containing patient Social Security numbers (SSNs). The SSNs must be encrypted at rest and in transit, and only authorized client applications should be able to decrypt them. Which technology should you use?

Accepted Answer

Always Encrypted with column master key stored in Azure Key Vault.. Always Encrypted is the correct choice because it ensures that sensitive data, such as SSNs, is encrypted both at rest and in transit, and the encryption keys are stored client-side (e.g., in Azure Key Vault). This design ensures that only authorized client applications with access to the column master key can decrypt the data, preventing even database administrators or cloud operators from viewing the plaintext values.

Answer

Row-level security (RLS) to restrict access based on user role.

Answer

Dynamic data masking (DDM) to mask SSNs for unauthorized users.

Answer

Transparent Data Encryption (TDE) with customer-managed keys.

Question 10

Your Azure SQL Database is experiencing high CPU usage. You suspect a specific query is causing the issue. You have enabled Query Store. How can you identify the query that has consumed the most cumulative CPU time over the last hour?

Accepted Answer

Use the Query Store Top Resource Consuming Queries report and filter by the last hour.. The Query Store Top Resource Consuming Queries report allows you to filter by time interval and shows cumulative CPU time, making it easy to identify the query consuming the most CPU over the last hour. Option A is wrong because sys.dm_exec_query_stats shows cached query plans and statistics since the last plan compilation, not cumulative over a specific time window. Option C is wrong because sys.dm_exec_requests shows currently running queries, not historical cumulative CPU usage. Option D is wrong because the Regressed Queries report shows queries whose performance has changed due to plan changes, not simply high CPU consumption.

Answer

Use sys.dm_exec_query_stats to sort by total_worker_time.

Answer

Use sys.dm_exec_requests to find queries with high CPU.

Answer

Use the Query Store Regressed Queries report.

Question 11

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

Accepted Answer

Set the TDE protector to the Key Vault key via the Azure portal or T-SQL. Setting the TDE protector to the Key Vault key is the step that actually enables customer-managed key (CMK) encryption for Azure SQL Database. This can be done via the Azure portal, PowerShell, Azure CLI, or T-SQL (using ALTER DATABASE SCOPED CONFIGURATION SET TDE_PROTECTOR). Without this step, the database continues to use a service-managed key.

Answer

Create a user-assigned managed identity for the SQL Database server

Answer

Store the encryption key in the SQL Database server's hardware security module (HSM)

Answer

Ensure the SQL Database server is inside the same virtual network as the Key Vault

Question 12

You have an Azure SQL Managed Instance with a database that is used for reporting. The reporting queries are read-only and can tolerate some latency. You want to offload the reporting workload from the primary instance to a secondary read-only replica. Which feature should you use?

Accepted Answer

Readable secondary in a failover group. The correct feature is a readable secondary in a failover group. This allows read-only queries to be routed to the secondary replica, offloading the reporting workload while maintaining high availability. SSIS (Option A) is an ETL tool, not for offloading read queries. Transaction replication (Option C) requires complex configuration and is not native to Managed Instance. Option D is incorrect because an auto-failover group does not support a read-write secondary; the secondary is always read-only.

Answer

SQL Server Integration Services (SSIS)

Answer

Transaction replication

Answer

Auto-failover group with read-write secondary

Question 13

You need to automate the creation of an Azure SQL Database using Azure CLI in a CI/CD pipeline. The database name must be unique and include the build ID. How should you specify the database name in the Azure CLI command?

Accepted Answer

Use an environment variable $(Build.BuildId) set by the pipeline. In Azure DevOps pipelines, $(Build.BuildId) is a predefined variable that provides a unique build ID. This can be used in Azure CLI commands to create a unique database name, e.g., 'mydb-$(Build.BuildId)'. Option A ($RANDOM) is Bash-specific and not reliable across pipeline runs. Option B (uuid) is not a valid Azure CLI function. Option C (hardcoded name) fails on pipeline rebuilds due to name conflicts.

Answer

Use the $RANDOM variable in Bash

Answer

Use the $(uuid) function in Azure CLI

Answer

Use a hardcoded name like 'mydb'

Question 14

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Accepted Answer

Memory grants pending. Memory grants pending indicates queries that are waiting for memory grants, which is a direct sign of memory pressure. Page life expectancy (PLE) measures how long pages remain in the buffer pool; a low PLE suggests that pages are being evicted frequently due to memory pressure. Therefore, both B and C are the best metrics to detect memory pressure. CPU percentage (D) indicates CPU pressure, and Data IO percentage (E) indicates I/O pressure, not memory pressure. Log IO percentage (A) relates to log write activity, not memory. Hence, the correct choices are B and C.

Answer

Log IO percentage

Answer

CPU percentage

Answer

Data IO percentage

Question 15

Your Azure SQL Managed Instance is experiencing high latency for write transactions. You have identified that log write latency is the bottleneck. The instance uses Premium SSD with 5000 IOPS and 200 MB/s throughput. You observe that the log file is 500 GB and has grown significantly. What is the most likely cause and solution?

Accepted Answer

The disk is not fast enough for random writes; switch to Ultra Disk.. For Azure SQL Managed Instance, log write latency is critical for transaction write performance. Premium SSD P30 with 5000 IOPS and 200 MB/s throughput may become a bottleneck under heavy write workloads. Switching to Azure Ultra Disk provides significantly lower latency and higher IOPS/throughput, which can reduce log write latency. Option D is incorrect because shrinking the transaction log does not improve performance and can cause severe fragmentation and performance degradation. Proper log backup scheduling is the correct method to manage log size.

Answer

Increase the log file size to allow better write performance.

Answer

The instance has insufficient CPU; scale up the managed instance.

Answer

The log file is too large causing fragmentation; shrink it to reduce latency.