DP-300 Practice Test 30 — 15 Questions

Question 1

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Option C is correct because reducing transaction log writes by batching transactions or using minimally logged operations reduces throughput. Option A is wrong because increasing max log size does not affect throughput. Option B is wrong because Premium tier is not allowed. Option D is wrong because log rate is not configurable.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 2

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

Accepted Answer

Use the Query Store reports in the Azure portal. Option C is correct because the Query Store is the primary tool in Azure SQL Database for tracking query performance, including CPU, IO, and duration. Option A is wrong because dynamic management views like sys.dm_exec_query_stats can be used but require more effort and may not persist history. Option B is wrong because Extended Events are for custom monitoring, not built-in query analysis. Option D is wrong because Azure SQL Database does not support SQL Server Profiler.

Answer

Use SQL Server Profiler

Answer

Create an Extended Events session to capture query events

Answer

Query sys.dm_exec_query_stats

Question 3

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). Option A is correct because the FORCE_LAST_GOOD_PLAN option in automatic tuning automatically forces a plan when a regression is detected. Option B is wrong because CREATE INDEX automates index creation, not plan forcing. Option C is wrong because DROP INDEX is for unused indexes. Option D is wrong because 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 4

You are tuning a query in Azure SQL Database that uses a nonclustered columnstore index. The query is supposed to use batch mode execution but shows row mode. What is the most likely cause?

Accepted Answer

The query does not have enough memory grant. Option D is correct because batch mode requires sufficient memory grant; if the query is memory-constrained, it may fall back to row mode. Option A is wrong because compatibility level 130 or higher supports batch mode. Option B is wrong because columnstore indexes support batch mode by default. Option C is wrong because MAXDOP setting does not disable batch mode.

Answer

The database compatibility level is below 130

Answer

The index is defined with a filter predicate

Answer

The query hint MAXDOP 1 is used

Question 5

You need to configure Azure SQL Database to automatically scale up based on CPU usage. Which feature should you use?

Accepted Answer

Autoscale settings for the database. Option B is correct because autoscale settings on the DTU or vCore model allow automatic scaling based on metrics like CPU. Option A is wrong because elastic pools are for managing multiple databases, not auto-scaling a single database. Option C is wrong because elastic jobs are for scheduling tasks. Option D is wrong because automatic tuning optimizes performance but does not scale resources.

Answer

Elastic job

Answer

Elastic pool

Answer

Automatic tuning

Question 6

You manage a critical Azure SQL Database that supports a financial application. The database is configured with the Business Critical service tier (8 vCores) and uses Always On availability groups for high availability. Recently, the application team noticed that some transactions are experiencing high latency during writes. You investigate and find that the log write latency is averaging 10 ms, and there are occasional HADR_SYNC_COMMIT waits. The application requires durable writes with synchronous commit. You need to reduce write latency without compromising data durability. What should you do?

Accepted Answer

Increase the number of vCores to 16. Option C is correct because increasing the number of vCores improves the log write throughput and reduces latency, as the log writer can use more CPU and memory. Option A is wrong because reducing the number of replicas is not possible in Business Critical (always 4). Option B is wrong because Premium SSD v2 may not be available in all regions and is not a direct fix for log write latency. Option D is wrong because turning off automatic tuning does not affect log write latency.

Answer

Switch to Premium SSD v2 storage

Answer

Disable automatic tuning to reduce overhead

Answer

Reduce the number of secondary replicas to minimize synchronization overhead

Question 7

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.. Options B and D are correct. Creating appropriate indexes reduces table scans. Updating statistics helps the optimizer choose better plans. Option A is wrong because increasing MAXDOP might not help and could cause issues. Option C is wrong because it increases tier cost. Option E is wrong because it does not address table scans.

Answer

Upgrade to Business Critical tier.

Answer

Increase MAXDOP to 8.

Answer

Enable automatic tuning.

Question 8

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

Accepted Answer

Create a clustered columnstore index on the table. Option A is correct because creating a clustered columnstore index on the table will significantly reduce IO by using columnar storage and eliminate key lookups; it also provides high compression to minimize storage. Option B is wrong because adding included columns to the nonclustered index will increase storage size and may not fully eliminate lookups. Option C is wrong because partitioning by customer ID can improve maintenance but may not reduce IO for point queries. Option D is wrong because creating a filtered index on customer ID is not helpful for many different customer IDs.

Answer

Create a filtered index on customer ID for frequent values

Answer

Partition the table by customer ID

Answer

Add all queried columns as included columns to the nonclustered index

Question 9

You are the database administrator for a company that uses Azure SQL Managed Instance. The instance hosts a mission-critical database that experiences periodic performance degradation. You need to set up a proactive monitoring solution that sends alerts when the average DTU usage exceeds 80% over a 5-minute period. The solution should minimize cost. What should you do?

Accepted Answer

Create an Azure Monitor metric alert on the 'dtu_consumption_percent' metric for the managed instance.. Option B is correct because Azure Monitor alerts on metrics like dtu_consumption_percent are cost-effective and meet the requirement. Option A is wrong because SQL Server Agent is for job scheduling, not metric alerts. Option C is wrong because Log Analytics workspaces cost money for ingestion. Option D is wrong because Elastic Database Jobs are for executing T-SQL across databases, not for alerts.

Answer

Create a SQL Server Agent job that checks sys.dm_db_resource_stats every 5 minutes and sends an email.

Answer

Use Elastic Database Jobs to run a query periodically and send alerts.

Answer

Stream diagnostic logs to a Log Analytics workspace and create a log alert rule.

Question 10

You are configuring performance optimization for an Azure SQL Database that uses the Hyperscale service tier. The database has heavy read-write workloads. Which THREE actions should you take to optimize performance?

Accepted Answer

Create nonclustered columnstore indexes on large fact tables.. Options A, C, and D are correct. Hyperscale benefits from nonclustered indexes on read-intensive tables, accelerated database recovery reduces log IO, and read-scale replicas distribute read load. Option B is wrong because Hyperscale does not support resource governor. Option E is wrong because increasing service tier is not an optimization action.

Answer

Configure resource governor to limit CPU usage.

Answer

Increase the service tier to Business Critical.

Question 11

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. Option A is correct because an elastic job agent is the top-level resource that orchestrates jobs. The job database stores job definitions and history. Option B is created after the agent. Option C is a target group, not first. Option D is a credential, but the agent must exist first.

Answer

A job database

Answer

A target group

Answer

A job credential

Question 12

Your company uses GitHub Actions to deploy changes to Azure SQL Database. You need to ensure that rollbacks can be performed automatically if a deployment fails. Which strategy should you implement?

Accepted Answer

Create a database copy or snapshot before each deployment. Option B is correct because deploying database snapshots before changes allows point-in-time recovery and rollback. Option A is for Azure SQL Managed Instance, not Database. Option C is not a standard rollback method. Option D is for code deployment, not database state.

Answer

Use Azure App Service deployment slots for the database

Answer

Use GitHub Actions deployment environments with approval gates

Answer

Use Azure SQL Managed Instance point-in-time restore

Question 13

You are automating the creation of an Azure SQL database. You need to ensure that the deployment is idempotent using Azure Resource Manager (ARM) templates. Which deployment mode should you use?

Accepted Answer

Incremental. Option B is correct because 'Incremental' mode processes the template as an incremental update to the resource group, making it idempotent. 'Complete' mode deletes resources not in the template, which is not idempotent. 'Validate' mode only validates without deploying, and 'Automatic' is not a valid ARM mode.

Answer

Complete

Answer

Automatic

Answer

Validate

Question 14

You have an Azure SQL Database that needs to be backed up daily using Azure Automation runbooks. The runbook must trigger an export of the database to a storage account. How should you configure the runbook to authenticate securely to Azure?

Accepted Answer

Enable a system-assigned managed identity for the Automation account. Option C is correct because Managed Identity (system-assigned or user-assigned) is the recommended secure authentication method for Azure Automation runbooks, avoiding stored credentials. Option A uses credentials stored in the runbook, which is less secure. Option B uses automation account credentials, which still requires key management. Option D is not a valid type.

Answer

Use a shared access signature (SAS) token stored in the runbook

Answer

Use Automation Account credential assets

Answer

Store the SQL admin credentials as variables in the runbook

Question 15

Your company uses Azure SQL Managed Instance and needs to automate patching and maintenance. The compliance team requires that all maintenance windows be predefined and that no maintenance occurs outside these windows. What should you configure?

Accepted Answer

Assign a maintenance configuration to the SQL Managed Instance. Option D is correct because Azure Maintenance Configurations allow you to schedule maintenance windows for Azure SQL Managed Instance, ensuring compliance. Option A is for Azure VMs, not SQL MI. Option B is for patching at the OS level, not applicable. Option C is for Azure SQL Database, not Managed Instance.

Answer

Configure a maintenance configuration for the virtual machine scale set

Answer

Set a maintenance window in the Azure SQL Database server settings

Answer

Use Azure Update Management to schedule patching