DP-300 Practice Test 13 — 15 Questions

Question 1

You manage a SQL Server on Azure VM. Users report slow queries during peak hours. You suspect memory pressure. Which metric should you monitor in Azure Monitor to confirm this?

Accepted Answer

Target Server Memory (KB). Target Server Memory (KB) shows the amount of memory SQL Server is trying to acquire; consistently high values relative to Total Server Memory indicate memory pressure. Option A is wrong because Page Life Expectancy is affected by memory but is a secondary indicator. Option B is wrong because Batch Requests/sec measures throughput, not memory. Option C is wrong because Buffer Cache Hit Ratio indicates cache efficiency but not directly memory pressure.

Answer

Page Life Expectancy

Answer

Batch Requests/sec

Answer

Buffer Cache Hit Ratio

Question 2

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Accepted Answer

Concurrent inserts into a table with a clustered index causing last-page contention.. PAGELATCH waits indicate contention on data pages, often due to concurrent inserts to the same page, such as the last page of a clustered index. Option B is wrong because buffer pool insufficient leads to PAGEIOLATCH waits, not PAGELATCH. Option C is wrong because high CPU usage due to inefficient queries causes CPU pressure, not directly PAGELATCH waits. Option D is wrong because long-running transactions blocking other queries results in lock waits, not page latch contention.

Answer

Insufficient buffer pool size leading to frequent reads from disk.

Answer

High CPU usage due to inefficient queries.

Answer

Long-running transactions blocking other queries.

Question 3

Which TWO methods can you use to monitor Azure SQL Database wait statistics?

Accepted Answer

Query sys.dm_os_wait_stats. Options A and E are correct. Querying sys.dm_os_wait_stats (A) directly provides cumulative wait statistics at the instance level. Query Store wait statistics reports (E) offer a query-focused view of wait statistics over time. Option B is incorrect because Azure Monitor Alerts are for setting notifications based on metrics or logs, not for monitoring wait statistics directly. Option C is incorrect because sys.dm_exec_query_stats shows execution statistics like CPU and duration, not wait types. Option D is incorrect because 'Dynamic Management Views' is a broad category, not a specific method; while some DMVs relate to waits, the generic term is not a valid answer.

Answer

Azure Monitor Alerts

Answer

Query sys.dm_exec_query_stats

Answer

Dynamic Management Views

Question 4

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

Accepted Answer

Automatic plan correction (FORCE_LAST_GOOD_PLAN). Automatic plan correction (FORCE_LAST_GOOD_PLAN) is the correct feature to automatically force a plan that performed better than the existing plan. Option A (DROP INDEX) and Option B (CREATE INDEX) are for index management, not plan forcing. Option D (Intelligent Insights) provides analysis but does not automatically force plans.

Answer

Automatic index management (DROP INDEX)

Answer

Automatic index management (CREATE INDEX)

Answer

Intelligent Insights

Question 5

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

Accepted Answer

Review the Query Store to identify the root cause of regression.. Query Store can show plan history and regression details, helping analyze why the forced plan is not optimal. Option A is wrong because disabling automatic tuning and creating a plan guide may be premature without first identifying the root cause. Option C is wrong because manually reverting to a previous plan using Query Store should not be the first action; you need to understand the regression first. Option D is wrong because scaling up the database does not directly address plan quality or regression.

Answer

Disable automatic tuning and create a plan guide.

Answer

Manually revert to the previous plan using Query Store.

Answer

Scale up the database to reduce resource pressure.

Question 6

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

Accepted Answer

Update statistics on the tables.. Updating statistics (A) helps the query optimizer generate more accurate execution plans, potentially avoiding table scans. Creating nonclustered indexes on columns used in WHERE clauses (E) can provide direct access paths, reducing the need for full table scans. Both actions improve performance without increasing costs. Option B (upgrading to Business Critical) increases cost and may not directly address table scans. Option C (increasing MAXDOP) can cause parallelism issues and is not guaranteed to reduce scans. Option D (enabling automatic tuning) is a feature that can suggest index and plan changes but is not as direct as updating statistics or creating indexes.

Answer

Upgrade to Business Critical tier.

Answer

Increase MAXDOP to 8.

Answer

Enable automatic tuning.

Question 7

You are designing an automated data movement solution using Azure Data Factory. Data must be copied from an on-premises SQL Server to Azure SQL Database. The solution must support incremental loads and handle schema changes. Which approach should you use?

Accepted Answer

Use Self-Hosted Integration Runtime with a Copy activity configured with incremental loading. A Self-Hosted Integration Runtime is required to access an on-premises SQL Server, and the Copy activity can be configured for incremental loading using a watermark column. This supports the requirement for incremental loads and schema changes (by using auto-create table). Option B is incorrect because Azure-SSIS IR is for SSIS package execution, not for data copy with incremental loading. Option C is incorrect because Azure IR cannot access on-premises networks without a self-hosted IR. Option D is incorrect because a Stored Procedure activity is used for executing stored procedures, not for copying data with incremental loading.

Answer

Use Azure-SSIS Integration Runtime with a Data Flow activity

Answer

Use Azure Integration Runtime with a Copy activity

Answer

Use a Stored Procedure activity in Data Factory

Question 8

Your team uses Azure DevOps to deploy database changes to Azure SQL Database. You need to automate the generation and deployment of database schema changes based on a Git branch merge. Which Azure service should you integrate?

Accepted Answer

Azure Pipelines with a release pipeline that uses the Azure SQL Database deployment task.. Azure Pipelines (part of Azure DevOps) can be configured to trigger on branch merges and includes a built-in Azure SQL Database deployment task for automating schema changes. Option A is wrong because Azure Automation Runbooks are not designed for CI/CD workflows. Option C is wrong because GitHub Actions would require moving outside the existing Azure DevOps ecosystem. Option D is wrong because Azure Logic Apps are meant for integration workflows, not CI/CD pipeline automation.

Answer

Azure Automation Runbook triggered by a webhook from Azure DevOps.

Answer

GitHub Actions with a workflow that runs on pull request merge, using the Azure SQL action.

Answer

Azure Logic Apps with an HTTP trigger from Azure DevOps, then use SQL connector to run scripts.

Question 9

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Accepted Answer

Build pipeline. A build pipeline (A) produces artifacts like a .dacpac file. A release pipeline (B) deploys those artifacts to the target database. A variable group (E) stores configuration values such as connection strings. Options C (Elastic job agent) and D (Azure Automation runbook) are not required for schema deployment via DevOps CI/CD. Elastic jobs are used for scheduled tasks across databases, not for deploying schema changes. Azure Automation runbooks automate processes outside of DevOps pipelines.

Answer

Elastic job agent

Answer

Azure Automation runbook

Question 10

You are a database administrator for an Azure SQL Database. You need to automate the deployment of schema changes across multiple databases in an elastic pool. Which Azure service should you use to orchestrate these deployments?

Accepted Answer

Azure DevOps. Azure DevOps with its release pipelines can manage the deployment of schema changes across multiple databases. Option A is wrong because Azure Functions is for event-driven code, not orchestrated deployments. Option B is wrong because Azure Automation primarily manages infrastructure tasks, not database schema deployments. Option D is wrong because Azure Logic Apps is for workflow integration, not database schema deployment.

Answer

Azure Functions

Answer

Azure Automation

Answer

Azure Logic Apps

Question 11

You have an Azure SQL Database that uses a failover group for high availability. You need to automate the failover to the secondary region during a planned maintenance window. What is the best approach?

Accepted Answer

Use Azure CLI or PowerShell to invoke planned failover. Azure SQL Database failover groups support planned failover via Azure CLI or PowerShell, which can be automated. Option B is wrong because Azure Automation runbooks can also be used, but the CLI is more direct. Option C is wrong because Elastic Jobs are not for failover. Option D is wrong because Azure Traffic Manager is for DNS-level traffic routing, not database failover.

Answer

Create an Azure Automation runbook that uses REST API

Answer

Schedule an Elastic Database Job to execute a failover script

Answer

Configure Azure Traffic Manager to route traffic to secondary

Question 12

Refer to the exhibit. You deploy this ARM template for an Azure SQL Database. The deployment succeeds. Which statement about the deployed database is true?

Accepted Answer

The database is provisioned tier. The ARM template specifies `requestedServiceObjectiveName: S2`, which corresponds to a provisioned tier (S2). The `autoPauseDelay` property is only applicable to serverless databases; when used with a provisioned tier, it is ignored and does not cause the deployment to fail. Therefore, the database is deployed as a provisioned tier S2 database. Option A is correct. Option B is incorrect because the database is not serverless. Option C is incorrect because `zoneRedundant` is set to false. Option D is incorrect because `maxSizeBytes` is 5 GB, which is less than 250 GB.

Answer

The database is in serverless tier

Answer

The database is zone redundant

Answer

The database maximum size is 250 GB

Question 13

You need to automatically send an email notification when an Azure SQL Database reaches 80% storage usage. What should you configure?

Accepted Answer

Azure Monitor alert with action group. Azure Monitor alerts can monitor metrics like 'storage_percent' for Azure SQL Database and trigger an action group configured to send email notifications. Option B (CDC with Logic Apps) captures data changes, not storage metrics. Option C (Elastic Database Job) is for executing T-SQL scripts across databases, not for alerts. Option D (SQL Agent Mail) is not available in Azure SQL Database.

Answer

Change Data Capture (CDC) with Logic Apps

Answer

Elastic Database Job with sp_send_dbmail

Answer

SQL Agent Mail

Question 14

A company uses Azure SQL Database with elastic pools. They need to automatically scale up the pool DTU when CPU usage exceeds 80% for 5 minutes and scale down when below 20% for 10 minutes. Which solution should they implement?

Accepted Answer

Use Azure Automation runbook with PowerShell to check metrics and scale. Azure SQL Database autoscale is not natively supported; scaling must be done programmatically. Azure Automation with PowerShell runbooks can query metrics and trigger scaling. SQL Agent jobs are not available in Azure SQL Database. Elastic Database Transactions are for distributed transactions, not scaling. Azure Logic Apps can also be used but are more complex for this scenario.

Answer

Use Elastic Database Transactions to handle scaling

Answer

Configure autoscale settings on the elastic pool in the Azure portal

Answer

Create a SQL Agent job to monitor and alter the pool

Question 15

Which THREE configurations are required to automate the deployment of database schema changes from a Git repository to Azure SQL Database using Azure Pipelines?

Accepted Answer

An Azure SQL Database deployment task in the pipeline.. Options B, C, and E are correct. An Azure SQL Database deployment task (e.g., SqlAzureSqlDatabaseDeployment task) executes the schema change scripts. An Azure Resource Manager service connection to the SQL Database is required for authentication and permissions. A YAML pipeline file defines the build and release stages including the deployment steps. Option A is incorrect because an Elastic Database Job agent is used for multi-database management, not for automated deployment from Git for a single database. Option D is incorrect because an Azure Automation Runbook is not part of Azure Pipelines; it is used for process automation outside of CI/CD.

Answer

An Elastic Database Job agent to run the deployment scripts.

Answer

An Azure Automation Runbook to execute the deployment.