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

Question 1

You manage an Azure SQL Database that supports a SaaS application. During a recent load test, you observed that the database frequently pauses inserts because of transaction log throughput limits. You need to identify the factor that most directly limits the maximum log rate for this database. What should you do?

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

Accepted Answer

Scale up the database to a higher service tier and compute size.. The maximum log rate in Azure SQL Database is directly tied to the compute resources allocated to the database, which are determined by the service tier and the number of vCores or DTUs. Scaling up increases these resources and thus the log throughput ceiling. Other actions like enabling ADR or increasing max size do not address the fundamental throughput limit.

Answer

Enable Accelerated Database Recovery (ADR).

Answer

Change the database to the Business Critical service tier.

Answer

Increase the database's max size.

Question 2

You are administering an Azure SQL Managed Instance that hosts a mission-critical database. You need to configure an alert that notifies the operations team when the database's transaction log space used exceeds 80% of its maximum size. Which action should you take?

Accepted Answer

Create an alert rule in Azure Monitor based on the 'log_space_used_percent' metric.. Azure Monitor provides built-in metrics for Azure SQL Managed Instance, including 'log_space_used_percent', which measures the percentage of transaction log space used. By creating an alert rule on this metric with a threshold of 80%, you can automatically notify the operations team when log space consumption is high, enabling proactive management of log growth.

Answer

Configure a SQL Server Agent job that queries sys.dm_db_log_space_usage and sends an email.

Answer

Create an alert rule in Azure Monitor based on the 'storage_space_used_mb' metric.

Answer

Enable automatic tuning and set the FORCE_LAST_GOOD_PLAN option.

Question 3

You are optimizing an Azure SQL Database that experiences frequent blocking. You enable Query Store and identify that a specific UPDATE statement is causing blocking due to lock escalation. You need to reduce blocking caused by this statement. Which two actions should you perform? (Choose two.)

Accepted Answer

Disable lock escalation on the table by using ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE).. Lock escalation converts many row or page locks into a table lock, causing blocking. Disabling lock escalation on the table prevents this conversion, directly reducing blocking. Enabling READ_COMMITTED_SNAPSHOT uses row versioning to reduce reader-writer blocking, which can also alleviate overall blocking. ADR, recovery model changes, and indexed views do not address lock escalation. Therefore, the two correct actions are to disable lock escalation and enable READ_COMMITTED_SNAPSHOT.

Answer

Create an indexed view on the table to reduce locking.

Answer

Enable Accelerated Database Recovery (ADR) on the database.

Answer

Set the database to the Simple recovery model.

Question 4

You are the database administrator for an Azure SQL Database that uses the General Purpose service tier. The database has a max size of 500 GB and a high volume of read operations. You notice that read queries are experiencing high PAGEIOLATCH_SH waits. You need to reduce these waits without changing the service tier. What should you do?

Accepted Answer

Scale up the database to a higher DTU or vCore count within the General Purpose tier.. PAGEIOLATCH_SH waits indicate that queries are waiting for data pages to be read from disk into the buffer pool. In the General Purpose tier, the primary way to reduce these waits without changing tiers is to increase compute resources (DTUs or vCores), which provides more memory for caching and higher IOPS. Read scale-out is unavailable in General Purpose, and increasing max size or enabling ADR does not address the I/O bottleneck.

Answer

Enable Accelerated Database Recovery (ADR).

Answer

Increase the database max size to 1 TB.

Answer

Enable read scale-out and redirect read-only workloads to the secondary replica.

Question 5

You are responsible for an Azure SQL Database that supports a customer-facing web application. The database is in the Hyperscale service tier. You need to monitor for situations where the database is approaching its storage limit and might become read-only. Which metric should you configure an alert on?

Accepted Answer

Storage percent. In Azure SQL Database Hyperscale, each database has a maximum storage size, and when usage approaches that limit, the database can transition to read-only. The storage percent metric tracks the percentage of the maximum storage size that is currently used, so an alert on it provides the necessary early warning. Other metrics such as DTU percent or data I/O percent measure compute or I/O governance, not storage capacity.

Answer

DTU percent

Answer

Data IO percent

Answer

Log write percent

Question 6

You are responsible for an Azure SQL Database that supports a web application. The database is configured with the Standard service tier, S3 performance level (100 DTUs). During peak hours, you notice that the DTU consumption regularly reaches 100%, causing timeouts. You need to increase the performance level to reduce DTU consumption percentage. What should you do?

Accepted Answer

Scale up the database to the Premium service tier.. The database is maxing out its DTU limit, causing timeouts. Scaling up to a higher service tier or performance level increases the available DTUs, so the same workload consumes a lower percentage of the total. Azure SQL Database does not support auto-scaling or read replicas for write scale-out in this context. Elastic pools share resources but do not inherently increase per-database resources.

Answer

Scale out the database by adding read replicas.

Answer

Enable auto-scaling for the database.

Answer

Create an elastic pool and move the database into it.

Question 7

You are responsible for an Azure SQL Managed Instance that hosts a mission-critical database. The database is configured with the Business Critical service tier. You need to ensure that the database can automatically fail over to a secondary replica within 30 seconds in the event of a primary replica failure. What should you configure?

Accepted Answer

Configure the database to use the Business Critical service tier with zone redundancy.. The Business Critical service tier in Azure SQL Managed Instance includes a built-in secondary replica for high availability. To protect against zone-level failures and achieve automatic failover within 30 seconds, you should enable zone redundancy. This ensures that the secondary replica is in a different availability zone, and the platform automatically handles failover without manual intervention.

Answer

Create an auto-failover group and set the grace period to 30 seconds.

Answer

Enable Active Geo-Replication and configure automatic failover.

Answer

Enable a failover group with automatic failover policy.

Question 8

You are managing an Azure SQL Database that hosts an online transaction processing (OLTP) application. The database is in the Business Critical service tier. You observe that the transaction log is growing rapidly and causing performance issues. You need to identify TWO actions that can reduce the log growth rate. (Choose two.)

Accepted Answer

Break large batch operations into smaller batches.. Reducing log growth rate involves minimizing the amount of logged operations and spreading them out. Index rebuilds are heavily logged, so reducing their frequency or switching to reorganize helps. Large batch operations generate bursts of log; breaking them into smaller batches reduces the peak rate. Changing service tier, enabling ADR, or using bulk-logged recovery do not directly lower the log generation rate.

Answer

Enable Accelerated Database Recovery (ADR).

Answer

Use bulk-logged recovery model.

Answer

Change the database to the General Purpose service tier.

Question 9

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to identify which factors can affect the maximum log generation rate for the database. (Choose two.)

Accepted Answer

The number of vCores allocated to the database.. The maximum log generation rate in Hyperscale is determined by the compute resources allocated to the database. Specifically, the number of vCores and the service level objective (SLO) dictate the log throughput. The SLO defines the vCore count and hardware generation, which directly affect log generation capacity. Other factors like log file size, number of replicas, and backup redundancy do not impact the maximum log generation rate.

Answer

The size of the transaction log file.

Answer

The number of secondary replicas configured for the database.

Answer

The backup storage redundancy option selected for the database.

Question 10

You are a database administrator for an Azure SQL Database that uses the Hyperscale service tier. You notice that a specific query performs poorly due to a suboptimal execution plan. You have identified a better plan in Query Store. You want to ensure the query uses the better plan without modifying the application code. What should you do?

Accepted Answer

Force the plan using Query Store's sys.sp_query_store_force_plan.. Query Store's plan forcing feature allows you to specify a plan for a query without changing application code. In Azure SQL Database, including Hyperscale, you can use sys.sp_query_store_force_plan to force the plan. This ensures the optimizer uses the identified efficient plan. Other methods like plan guides are unsupported, USE PLAN requires code changes, and statistics updates do not guarantee plan selection.

Answer

Create a manual statistics update on the affected table.

Answer

Add a USE PLAN hint to the query in the application.

Answer

Create a plan guide using sp_create_plan_guide.

Question 11

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

Accepted Answer

Identify and create missing indexes.. High CPU usage in an S3 Azure SQL Database often stems from inefficient query plans caused by missing indexes. Creating appropriate indexes reduces the number of rows scanned and the CPU cycles needed for operations like key lookups and sorting, directly lowering CPU consumption without changing the service tier.

Answer

Increase the maximum number of concurrent workers.

Answer

Reduce MAXDOP to 1.

Answer

Increase MAXDOP to 8.

Question 12

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

Accepted Answer

Missing nonclustered index; create an index on the predicates.. Query Performance Insight shows a query with high duration and CPU usage, and the query plan reveals a clustered index scan. A clustered index scan reads all rows in the table, which is inefficient when only a subset of rows is needed. The most likely cause is a missing nonclustered index on the columns used in the WHERE clause (predicates), which would allow a seek operation instead of a full scan, reducing both CPU and duration.

Answer

Fragmented clustered index; rebuild the clustered index.

Answer

Insufficient memory; increase the service tier.

Answer

Parameter sniffing; add OPTION (RECOMPILE).

Question 13

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

Accepted Answer

Automatic tuning. Automatic tuning in Azure SQL Database continuously analyzes query execution plans and workload patterns, then automatically creates, drops, or rebuilds indexes to improve performance. It uses built-in intelligence to recommend and apply index changes without manual intervention, making it the correct feature for automatically adjusting indexing based on workload patterns.

Answer

Azure Advisor

Answer

Intelligent Insights

Answer

Query Store

Question 14

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

Accepted Answer

Query Store. Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Answer

Data classification

Answer

Server-level audit

Answer

Diagnostic settings for SQLInsights

Question 15

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

Accepted Answer

I/O subsystem bottleneck. The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Answer

Blocking

Answer

CPU bottleneck

Answer

Memory pressure