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

Question 1

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

Accepted Answer

The transaction log is not being truncated, possibly due to an active transaction or replication. The exhibit shows a transaction log with very low free space (0.01%) and a high log used percentage (99.99%), but the data file has ample free space. This indicates the transaction log is not being truncated, likely due to an active transaction preventing log reuse or a replication scenario that marks log records as needed. In Azure SQL Database, log space is managed automatically, but long-running transactions or replication can block log truncation, causing log space full errors even when storage is not full.

Answer

The database storage is almost full, preventing log growth

Answer

The log rate limit is being throttled due to high log IO percentage

Answer

The database should be scaled to BusinessCritical tier for faster log writes

Question 2

Refer to the exhibit. You notice the database db1 is currently 80% full on storage, and the service objective is S3. Which action would best prevent storage full errors while minimizing cost?

Accepted Answer

Increase the maximum database size to 250 GB.. The database is 80% full and uses the S3 service objective, which has a default max size of 100 GB. Increasing the maximum database size to 250 GB directly addresses the storage constraint without changing the compute tier, thus avoiding additional cost. This is the most cost-effective action because it only expands storage capacity while keeping the same performance level and price for compute.

Answer

Enable automatic storage growth on the database.

Answer

Change the service objective to S4.

Answer

Scale the database to a higher service tier like Standard S4.

Question 3

Refer to the exhibit. You observe DTU usage reaching 100% at 11:00 AM. Which recommendation would best resolve the performance issue?

Accepted Answer

Schedule the recurring job to run at a different time, such as midnight.. The exhibit shows a pattern of increasing DTU usage. The recurring job runs from 10:00 to 10:30, but the peak at 11:00 suggests another process causing full usage. The best solution is to schedule resource-intensive operations during off-peak hours. Increasing the service tier may help but is not the most targeted approach.

Answer

Enable read scale-out to offload reporting queries.

Answer

Increase the service tier to S3 to provide more DTUs.

Answer

Create an index on the tables used by the job.

Question 4

Refer to the exhibit. A user reports being unable to connect to the database. What is the most likely cause?

Accepted Answer

The user is using an incorrect password.. The exhibit (not shown here) typically displays a login failure error such as 'Login failed for user' with error 18456, state 8, which specifically indicates a password mismatch. State 8 in SQL Server error 18456 means the password provided is incorrect. This is the most likely cause when the user can reach the server but authentication fails.

Answer

The user account is locked out due to too many failed login attempts.

Answer

The user does not have permission to access the database.

Answer

The server firewall is blocking the IP address.

Question 5

Refer to the exhibit. You are managing db1, which is currently in Standard tier. What is the maximum size you can set for db1 without changing the service objective?

Accepted Answer

268,435,456,000 bytes (250 GB). The first command shows that Standard S2 supports a max size of 268435456000 bytes (250 GB). The second command shows the current max size is 107374182400 bytes (100 GB). Since db1 is already on S2, you can increase the max size up to 250 GB within the same service objective.

Answer

107,374,182,400 bytes (100 GB)

Answer

1,073,741,824,000 bytes (1 TB)

Answer

536,870,912,000 bytes (500 GB)

Question 6

You are a database administrator for a financial application hosted on Azure SQL Managed Instance. The application experiences blocking during peak hours. You run a query against sys.dm_exec_requests and notice a high number of sessions with a wait type of LCK_M_X. You need to identify the blocking chain and the root blocker. Which dynamic management view (DMV) should you use to find the session ID of the root blocker?

Accepted Answer

sys.dm_exec_requests joined with sys.dm_exec_sessions recursively. The root blocker is the session that is not blocked by any other session but is blocking others. While sys.dm_exec_requests shows blocking_session_id, it only gives the immediate blocker. To find the root, you must recursively traverse the blocking chain using a recursive CTE that joins sys.dm_exec_requests to itself until blocking_session_id is NULL. This is the standard method to identify the root blocker.

Answer

sys.dm_os_waiting_tasks

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Question 7

You are administering an Azure SQL Managed Instance that hosts several databases. You need to ensure that the `tempdb` database does not become a performance bottleneck due to excessive allocation contention. Which configuration should you implement?

Accepted Answer

Increase the number of tempdb data files to match the number of logical processors, up to 8.. To alleviate tempdb allocation contention in Azure SQL Managed Instance, the recommended approach is to add multiple tempdb data files, ideally one per logical processor up to 8, and then increase further if needed. This distributes allocation requests across files, reducing contention on allocation pages. Other options are either unsupported, already enabled, or counterproductive.

Answer

Enable Trace Flag 1118 globally on the instance.

Answer

Configure tempdb to use memory-optimized metadata.

Answer

Set the maximum degree of parallelism (MAXDOP) to 1 for the instance.

Question 8

You are the database administrator for an Azure SQL Database that hosts a high-volume OLTP application. You observe that the database frequently reaches its maximum log write throughput, causing transaction latency. You need to identify which factor is most likely limiting the log rate. What should you do first?

Accepted Answer

Check the sys.dm_db_log_stats dynamic management view for log generation and throughput statistics.. sys.dm_db_log_stats is the most appropriate first step because it delivers specific metrics on log generation and flush behavior, enabling you to determine if the log rate is the limiting factor. Other options either show symptoms or require prior confirmation of the bottleneck.

Answer

Query sys.dm_os_wait_stats and look for WRITELOG waits.

Answer

Review the Azure SQL Database service tier and compute size limits.

Answer

Examine the sys.dm_db_resource_stats view for log_write_percent.

Question 9

You administer an Azure SQL Database that supports a high-throughput order processing application. The database is in the General Purpose service tier. During month-end batch processing, users report that transactions are slow. You query sys.dm_db_resource_stats and observe that the log_write_percent metric frequently reaches 100%, while data_io_percent remains below 40%. You need to reduce log write pressure to improve transaction throughput. What should you do first?

Accepted Answer

Increase the number of vCores for the database.. The log_write_percent metric at 100% indicates the transaction log write rate is the limiting factor. In Azure SQL Database, log write throughput scales with vCore count. Increasing vCores raises the log write limit and reduces contention, improving transaction throughput. Other options either do not affect log write rate or address different resources.

Answer

Scale up the database to the Business Critical service tier.

Answer

Increase the database's max size to allow more log growth.

Answer

Enable Accelerated Database Recovery (ADR) on the database.

Question 10

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You observe that the database is experiencing frequent page latch waits on non-leaf pages of a B-tree index. You need to reduce these waits. Which two actions should you perform? (Choose two.)

Accepted Answer

Change the index to use a nonclustered columnstore index.. Non-leaf page latch contention in B-tree indexes can be reduced by partitioning the index, which spreads the B-tree structure across multiple partitions, and by converting to a columnstore index, which does not have non-leaf pages. Page compression and OPTIMIZE FOR UNKNOWN do not address the concurrency issue, and OPTIMIZE_FOR_SEQUENTIAL_KEY targets last-page contention, not non-leaf pages.

Answer

Use OPTIMIZE FOR UNKNOWN on queries that access the index.

Answer

Enable last-page insert contention reduction by using OPTIMIZE_FOR_SEQUENTIAL_KEY.

Answer

Enable page compression on the index.

Question 11

You manage an Azure SQL Database that uses the vCore purchasing model. The database is experiencing high PAGELATCH_EX waits on tempdb allocation pages. You need to reduce these waits. What should you do?

Accepted Answer

Enable memory-optimized tempdb metadata.. PAGELATCH_EX waits on tempdb allocation pages are typically caused by contention on system pages like PFS, GAM, and SGAM. Enabling memory-optimized tempdb metadata removes this contention by storing metadata in memory-optimized tables. This feature is available in Azure SQL Database and is the recommended solution. Other options do not address the root cause.

Answer

Increase the database max size.

Answer

Configure active geo-replication.

Answer

Increase the number of tempdb data files.

Question 12

You are tuning an Azure SQL Database that uses the General Purpose service tier. The database has a high volume of read operations and you notice a significant amount of PAGEIOLATCH_SH waits. You have already optimized queries and indexes. What should you do to reduce these waits?

Accepted Answer

Scale up the database to a higher service tier or compute size to increase memory and IOPS.. PAGEIOLATCH_SH waits indicate that queries are waiting for data pages to be read from storage. In the General Purpose tier, storage performance is constrained. Scaling up the database increases memory and IOPS, which can reduce the need for physical reads and speed up I/O, thus alleviating the waits. Other options are either unavailable in this tier or not targeted at the root cause.

Answer

Configure In-Memory OLTP for the most frequently accessed tables.

Answer

Increase the maximum degree of parallelism (MAXDOP) for the database.

Answer

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

Question 13

You manage an Azure SQL Database (General Purpose, provisioned) that runs a reporting workload. Users report that an overnight ETL job occasionally runs much longer than usual. You need to determine whether the slowdown correlates with resource governance limits applied to the database. Which metric should you review in Azure Monitor?

Accepted Answer

Resource usage stats. Resource usage stats aggregate the key governance dimensions for an Azure SQL Database, making it the most efficient metric to check when you suspect resource limits are causing intermittent slowdowns. Unlike single-resource metrics such as CPU percent or log write percent, it exposes all relevant limits together, so you can identify the actual bottleneck without checking each metric individually.

Answer

Log write percent

Answer

CPU percent

Answer

Workers percent

Question 14

You are optimizing an Azure SQL Database that uses the General Purpose service tier. The database experiences high tempdb usage during large reporting queries that use sorting and hashing. You need to reduce tempdb contention and improve query performance. Which two actions should you perform? (Choose two.)

Accepted Answer

Scale up to a higher service tier or more vCores.. Scaling up increases compute resources and the number of tempdb data files, which reduces allocation contention and provides more memory for sorting and hashing. Enabling memory-optimized tempdb metadata eliminates latch contention on tempdb system tables. Together, these actions directly address high tempdb usage and contention during large reporting queries, improving overall performance.

Answer

Enable Accelerated Database Recovery (ADR).

Answer

Enable read scale-out to route reporting queries to a readable secondary.

Answer

Increase the database's max size to allow tempdb to grow.

Question 15

You are managing an Azure SQL Database that uses the Hyperscale service tier. You notice that read-only queries are causing contention on the primary compute replica. You need to offload read-only workloads to a secondary replica. What should you do?

Accepted Answer

Enable read-scale out by setting the ApplicationIntent=ReadOnly in the connection string.. The correct action is to enable read-scale out by setting ApplicationIntent=ReadOnly in the connection string. Hyperscale supports read-scale out, which routes read-only connections to a secondary replica, reducing primary contention. Other options involve geo-replication, manual copies, or changing tiers, which are either not designed for this purpose or unnecessary.

Answer

Configure active geo-replication to a secondary database in another region.

Answer

Create a read-only database copy and redirect queries to it.

Answer

Use the Business Critical service tier, which includes a built-in secondary replica.