DP-300 Monitor, configure, and optimize database resources Practice Test 14 — 15 Questions

Question 1

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

Accepted Answer

Configure minimum and maximum eDTU per database in the pool. Option B is correct because configuring minimum and maximum eDTU per database ensures that each database gets guaranteed resources when needed, while still sharing the pool. Option A is wrong because moving to separate standalone databases would increase cost. Option C is wrong because using a vCore pool instead of DTU does not solve the issue inherently. Option D is wrong because adding more databases to the pool increases contention.

Answer

Migrate the pool to a vCore-based elastic pool

Answer

Add more databases to the pool to spread the load

Answer

Move each database to a standalone DTU tier

Question 2

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 3

You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the General Purpose tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?

Accepted Answer

Create a read-only replica and direct reports to it. Option D is correct because creating a read-only replica allows you to offload reporting queries to the replica, which can be at a lower service tier, reducing load on the primary and improving performance. Option A is wrong because scaling up the primary to S3 during reporting hours increases cost. Option B is wrong because adding indexes helps but may not be sufficient. Option C is wrong because columnstore indexes are not ideal for OLTP workloads and may affect write performance.

Answer

Increase the service tier to S3 during the end-of-month period

Answer

Add nonclustered indexes to the tables used in reports

Answer

Convert the tables to clustered columnstore indexes

Question 4

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

Accepted Answer

Use Query Store to identify and force a more efficient execution plan.. Option A is correct because enabling Query Store and forcing a plan that reduces CPU is a non-intrusive way to improve performance. Option B is wrong because it increases DTUs, which changes service tier. Option C is wrong because it adds indexes. Option D is wrong because scaling up is changing tier.

Answer

Scale up to the Business Critical tier.

Answer

Increase the DTUs to 200.

Answer

Add a nonclustered index on the join columns.

Question 5

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 6

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

Accepted Answer

Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).. Option C is correct because changing the secondary replica's backup storage redundancy from locally-redundant storage (LRS) to geo-redundant storage (GRS) might cause increased latency, but here the issue is high log write latency. Actually, the correct answer is to change the secondary's backup storage redundancy to LRS (if it is GRS) to reduce latency. However, the options are as given. Option A is wrong because decreasing the secondary replica's service tier reduces latency but also reduces redundancy. Option B is wrong because asynchronous commit reduces protection. Option D is wrong because adding more replicas increases cost and complexity. The best answer is C, as it addresses the storage latency issue without compromising data protection.

Answer

Add an additional secondary replica to distribute the log write load.

Answer

Change the failover group to use asynchronous commit mode.

Answer

Decrease the service tier of the secondary replica to General Purpose.

Question 7

You are a database administrator for a SaaS company. You manage an Azure SQL Database that uses the Hyperscale service tier. The database supports a multi-tenant application with unpredictable workloads. You notice that the database's page server is experiencing high IOPS utilization, causing query performance degradation. You need to improve performance by optimizing the page server usage. What should you do?

Accepted Answer

Enable accelerated database recovery (ADR) to reduce log IO.. Option A is correct because enabling accelerated database recovery reduces the log generation and IO on page servers. Option B is wrong because increasing the number of replicas does not reduce page server IO. Option C is wrong because changing to Business Critical might not address page server IO; Hyperscale has its own architecture. Option D is wrong because increasing max degree of parallelism might increase IO.

Answer

Increase the number of secondary replicas to distribute read workload.

Answer

Change the service tier to Business Critical to get faster IO.

Answer

Increase the MAXDOP setting for the database.

Question 8

You manage an Azure SQL Database that uses the General Purpose tier. You need to monitor the performance of the database and identify the top resource-consuming queries. You want to use a built-in feature that requires no additional cost. What should you use?

Accepted Answer

Query Store.. Option D is correct because Query Store is a built-in, no-cost feature that tracks query performance. Option A is wrong because Azure SQL Analytics is a paid solution. Option B is wrong because dynamic management views require manual querying. Option C is wrong because SQL Server Profiler is deprecated and not supported in Azure SQL Database.

Answer

Azure SQL Analytics (preview) in Azure Monitor.

Answer

SQL Server Profiler.

Answer

sys.dm_exec_query_stats and sys.dm_exec_sql_text DMVs.

Question 9

You are a database administrator for an e-commerce company that uses Azure SQL Managed Instance. You have a database that experiences high blocking due to long-running transactions. You need to configure a solution that automatically terminates the blocking process after it has been waiting for more than 5 seconds. The solution should be set at the database level and should not require any application changes. What should you do?

Accepted Answer

Set the DEADLOCK_PRIORITY to LOW on the blocking session and set LOCK_TIMEOUT to 5000.. Option A is correct because setting the DEADLOCK_PRIORITY to LOW and using a lock timeout of 5 seconds will cause the blocking session to be killed if it exceeds the timeout. Option B is wrong because resource governor is not available in Managed Instance at the database level. Option C is wrong because read committed snapshot isolation does not kill blocking processes. Option D is wrong because changing transaction isolation level does not automatically terminate processes.

Answer

Enable read committed snapshot isolation (RCSI) on the database.

Answer

Create a resource governor workload group with a maximum grant time of 5 seconds.

Answer

Set the transaction isolation level to SNAPSHOT for all sessions.

Question 10

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 11

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

Accepted Answer

Azure Monitor Metrics. Options A, B, and D are correct. Query Store, DMVs, and Azure Metrics are built-in and free. Option C is wrong because SQL Server Profiler is deprecated and not supported. Option E is wrong because Performance Insights is not an Azure SQL feature.

Answer

Performance Insights

Answer

SQL Server Profiler

Question 12

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 13

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

Accepted Answer

Enable compression on transaction log backups.. Option B is correct because compressing transaction log backups reduces log volume indirectly, but more directly, enabling accelerated database recovery (ADR) reduces log IO. Since ADR is not an option here, the best answer is to use compression on backups. Option A is wrong because increasing service tier does not reduce log generation. Option C is wrong because adding indexes increases log writes. Option D is wrong because backup retention does not affect log generation.

Answer

Create additional nonclustered indexes on frequently updated tables.

Answer

Increase the service tier to Business Critical.

Answer

Increase the backup retention period.

Question 14

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Accepted Answer

In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.. Option C is correct because diagnostic settings can stream metrics to Log Analytics. Option A is wrong because Query Store does not send metrics to Log Analytics. Option B is wrong because DMVs do not stream automatically. Option D is wrong because SQL Insights is a paid solution.

Answer

Enable Azure SQL Insights (preview) for the database.

Answer

Enable Query Store and configure it to export to Log Analytics.

Answer

Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.

Question 15

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