DP-300 Practice Test 30 — 15 Questions

Question 1

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to reduce the time it takes to perform a database restore. Which TWO factors directly affect the restore time? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

The number of transaction log records that need to be replayed.. In Hyperscale, restore performance is primarily determined by the amount of transaction log that must be replayed and the number of page servers that need to be attached. Data file size and backup redundancy do not directly affect restore time, and the SLO influences resources but not the fundamental restore process.

Answer

The backup storage redundancy option.

Answer

The service level objective (SLO) of the database.

Answer

The size of the database's data files.

Question 2

You manage an Azure SQL Database that runs a reporting workload. Users report that queries against a large fact table sometimes take much longer than usual, and you suspect that a plan regression occurred after a recent statistics update. You need to identify which queries have a plan that changed and performed worse, without deploying any external monitoring tools. What should you use?

Accepted Answer

Query Store's Regressed Queries view. Query Store persists query text, plans, and runtime statistics inside the database and includes a Regressed Queries view that compares plan performance across time windows. When a statistics update causes a plan change that degrades a reporting query, that view surfaces the affected queries with their historical plans and metrics, letting you force a known-good plan or take other corrective action without external monitoring.

Answer

SQL Server Profiler traces collected to a file

Answer

Azure SQL Database automatic tuning's CREATE INDEX recommendations

Answer

sys.dm_db_index_physical_stats

Question 3

You manage an Azure SQL Database that uses the Business Critical service tier. A critical reporting query normally completes in under 5 seconds but occasionally takes over 60 seconds. You observe that during these slow executions, the query uses a different execution plan that performs a large number of physical reads. You need to ensure that the fast plan is used consistently for this query. What should you do?

Accepted Answer

Use Query Store to force the fast execution plan for the query.. Query Store captures all plans generated for a query and provides a mechanism to force a specific plan. When a query occasionally uses a suboptimal plan that causes high physical reads, you can identify the fast plan in Query Store and force it, ensuring consistent performance. This approach is native to Azure SQL Database and allows you to monitor and easily revert if needed.

Answer

Increase the database's compute size to reduce physical reads.

Answer

Enable automatic tuning FORCE_LAST_GOOD_PLAN on the database.

Answer

Create a plan guide using sp_create_plan_guide for the query.

Question 4

You are monitoring an Azure SQL Database that uses the vCore purchasing model. You need to set up alerts to notify you when the database approaches its resource limits. Which two metrics should you alert on to detect CPU and I/O pressure? (Choose two.)

Accepted Answer

physical_data_read_percent. The correct metrics are cpu_percent and physical_data_read_percent. cpu_percent directly measures CPU utilization, and physical_data_read_percent measures the percentage of physical data reads, indicating I/O pressure. These are core metrics in the vCore model for monitoring resource consumption. Other metrics like log_write_percent, sessions_percent, and workers_percent are less directly related to the overall CPU and I/O pressure.

Answer

log_write_percent

Answer

sessions_percent

Answer

workers_percent

Question 5

You are deploying an Azure SQL Managed Instance for a legacy application that requires SQL Server Agent jobs and cross-database queries. The instance must be able to access an on-premises file share for backup and restore operations. You need to configure network connectivity so that the managed instance can communicate with the on-premises network. What should you implement?

Accepted Answer

Configure a site-to-site VPN or ExpressRoute between the on-premises network and the Azure virtual network that hosts the managed instance.. Azure SQL Managed Instance is deployed in a virtual network subnet and requires a site-to-site VPN or ExpressRoute to communicate with on-premises resources. This provides a private, secure connection that allows the managed instance to access on-premises file shares for backup and restore. Public endpoints, VNet peering, and Private Link do not provide the necessary outbound connectivity to on-premises networks.

Answer

Enable a public endpoint on the managed instance and allow access from the on-premises public IP address.

Answer

Create a VNet peering between the managed instance subnet and the on-premises network.

Answer

Configure an Azure Private Link endpoint for the managed instance and connect it to the on-premises network.

Question 6

You need to configure monitoring for an Azure SQL Database to meet the following requirements: 
- Alert when average DTU consumption exceeds 90% for 10 minutes.
- Track failed logins.
- Analyze query performance over the last 30 days.
Which THREE Azure services or features should you use? (Choose three.)

Accepted Answer

SQL auditing. Azure Monitor metric alerts (D) are the correct mechanism to trigger on a metric threshold such as average DTU consumption exceeding 90% over a 10-minute window, since DTU percentage is exposed as a platform metric and metric alerts support aggregation and time-window conditions. SQL auditing (A) records login events including failed logins to an audit log or Log Analytics, which directly satisfies the requirement to track failed logins. Query Store (C) is the built-in feature that persists query execution statistics and plans, allowing analysis of query performance over a retention period such as the last 30 days. Automatic tuning (B) is a feature that applies index and plan corrections, not a monitoring or alerting tool, so it does not meet any of the stated requirements. Azure SQL Assessment (E) evaluates configuration best practices and migration readiness, not runtime DTU alerting, login tracking, or query performance history.

Answer

Automatic tuning

Answer

Azure SQL Assessment

Question 7

You are configuring alerts for an Azure SQL Database. You need to create an alert that fires when the database's DTU consumption exceeds 80% for a sustained period. Which Azure Monitor metric should you use?

Accepted Answer

DTU percentage. The DTU percentage metric provides the percentage of DTU consumption against the database's limit. Setting an alert on this metric with a threshold of 80% will notify when DTU usage exceeds that level, directly meeting the requirement. Other metrics like CPU or storage do not capture the full DTU picture.

Answer

Sessions count

Answer

Storage percent

Answer

CPU percent

Question 8

You have an Azure SQL Database that must be automatically restarted every night to clear the procedure cache. You plan to use elastic jobs in Azure SQL Database. What should you create first?

Accepted Answer

An elastic job agent. To use elastic jobs in Azure SQL Database, you must first create an elastic job agent. The agent is the compute resource that runs the jobs. After creating the agent, you create a job database, target groups, credentials, and jobs. The agent is the foundational component.

Answer

A job database

Answer

A target group

Answer

A job credential

Question 9

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in waits of type RESOURCE_SEMAPHORE. Which action should you take to resolve the issue?

Accepted Answer

Identify and optimize queries with large memory grants. RESOURCE_SEMAPHORE waits are caused by queries waiting for memory grants. The most effective resolution is to identify queries with large memory grants and optimize them to reduce their memory requirements. This directly reduces contention and improves performance, rather than merely adding more resources.

Answer

Increase the database's DTU or vCore limit

Answer

Increase the database's max memory

Answer

Enable Query Store to capture the queries causing the waits

Question 10

You are evaluating Azure SQL Database for a new application that requires the database to be isolated from other Azure tenants and to have a dedicated compute and storage resources. The application also requires support for SQL Server Agent and cross-database queries. Which Azure SQL deployment option should you choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the only Azure SQL deployment option that provides a dedicated instance with support for SQL Server Agent, cross-database queries, and network isolation via a dedicated subnet. Single database and elastic pools do not support these features. Hyperscale is a service tier, not a deployment option, and lacks these capabilities. Therefore, Azure SQL Managed Instance is the correct choice.

Answer

Azure SQL Database hyperscale

Answer

Azure SQL Database elastic pool

Answer

Azure SQL Database single database

Question 11

You are responsible for an Azure SQL Database that hosts a financial application. The database is in the General Purpose service tier. You need to ensure that the database automatically scales compute resources based on workload demand without manual intervention. What should you configure?

Accepted Answer

Enable serverless compute tier for the database.. The serverless compute tier in Azure SQL Database automatically scales compute based on workload and can pause during inactivity. It is designed for single databases with intermittent or unpredictable usage. Unlike manual scaling or elastic pools, serverless requires no manual intervention to adjust resources. Automatic tuning optimizes queries but does not scale compute. Therefore, enabling serverless compute tier is the correct choice to achieve automatic scaling without manual intervention.

Answer

Create an elastic pool and add the database to it.

Answer

Enable automatic tuning for the database.

Answer

Configure auto-scaling in the Azure portal for the database.

Question 12

You are managing an Azure SQL Database that uses the Business Critical service tier. You need to ensure that the database can handle a sudden increase in transaction log write throughput without experiencing log write waits. Which factor should you primarily consider?

Accepted Answer

The number of vCores allocated to the database.. In the Business Critical service tier, the maximum transaction log write throughput is directly proportional to the number of vCores allocated to the database. To handle increased log write throughput, you should scale up the number of vCores. Other factors such as database size, read replicas, and backup storage redundancy do not affect the log write rate limit.

Answer

The configured backup storage redundancy.

Answer

The number of read replicas configured.

Answer

The size of the database in gigabytes.

Question 13

You are a database administrator for an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the instance is experiencing high PAGELATCH_EX waits on tempdb allocation pages. You need to reduce this contention without changing the service tier. What should you do?

Accepted Answer

Add more tempdb data files.. PAGELATCH_EX waits on tempdb allocation pages are caused by multiple concurrent sessions trying to allocate space on the same pages. Adding more tempdb data files spreads the allocation across multiple files, reducing contention. This is a well-known tuning technique for tempdb. Other options do not address the root cause: ADR affects recovery, Resource Governor limits resources, and faster storage addresses I/O waits, not latch contention. Therefore, adding more tempdb data files is the correct solution.

Answer

Configure Resource Governor to limit tempdb usage.

Answer

Enable Accelerated Database Recovery (ADR) on the instance.

Answer

Move the tempdb files to a faster storage tier.

Question 14

You manage an Azure SQL Database named SalesDB in the East US region, Business Critical tier. The database has a long-term retention policy that stores weekly full backups in a geo-redundant storage account. During a compliance audit, you need to prove that you can recover SalesDB to a point in time in the event of a complete East US regional outage. You must perform a geo-restore to the West US region. What is the maximum retention period for point-in-time restore that you can expect when performing this geo-restore?

Accepted Answer

The most recent geo-replicated backup, which is typically within one hour of the current time.. Geo-restore for Azure SQL Database uses the most recent geo-replicated backup, which is typically within one hour. Unlike point-in-time restore, which allows restoring to any point within the retention period (up to 35 days), geo-restore does not provide a point-in-time capability; it restores to the latest geo-replicated backup. This is an important distinction when planning for disaster recovery across regions.

Answer

Up to 35 days, because geo-restore uses the same retention period as standard point-in-time restore.

Answer

Up to 14 days, because the long-term retention policy retains weekly full backups that can be used for geo-restore.

Answer

Up to 7 days, because geo-replicated backups are retained for 7 days by default.

Question 15

You are the database administrator for a logistics company that uses Azure SQL Database. A nightly Azure Automation runbook must trigger a stored procedure in the database after each successful run. The runbook authenticates to Azure using a system-assigned managed identity for the Automation account. You need to grant the managed identity the least-privilege permissions required to execute the stored procedure. Which two actions should you perform? (Choose two.)

Accepted Answer

Run GRANT EXECUTE ON OBJECT::dbo.usp_ProcessShipments TO [automation-account-name] in the target database.. Managed identities for Azure Automation require a contained database user mapped via FROM EXTERNAL PROVIDER before permissions can be granted. Once that user exists, the least-privilege permission for running a single stored procedure is EXECUTE on that object. Together these two actions let the runbook invoke the procedure without over-provisioning rights or introducing stored credentials.

Answer

Store the database administrator password in an Automation variable and use SQL authentication in the runbook.

Answer

Create a database user for the managed identity and add it to the db_owner fixed database role.

Answer

Assign the managed identity the Contributor role on the Azure SQL logical server resource.