Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 181–185 (page: 38)

Updated On: 10-Feb-2026

You have an Azure SQL managed instance named SQL1 and two Azure web apps named App1 and App2.

You need to limit the number of IOPs that App2 queries generate on SQL1.

Which two actions should you perform on SQL1? Each correct answer presents part of the solution.

Note: Each correct selection is worth one point.

  1. Enable query optimizer fixes.
  2. Enable Resource Governor.
  3. Enable parameter sniffing.
  4. Create a workload group.
  5. Configure In-memory OLTP.
  6. Run the Database Engine Tuning Advisor.
  7. Reduce the Max Degree of Parallelism value.

Answer(s): B,D

Explanation:

SQL Server Resource Governor is a feature that you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical I/O, and memory that incoming application requests can use.
The following concept is fundamental to understanding and using Resource Governor:
* Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload allows for aggregate monitoring of the sessions, and defines policies for the sessions. Each workload group is in a resource pool. Two workload groups (internal and default) are created and mapped to their corresponding resource pools when SQL Server is installed. Resource Governor also supports user-defined workload groups.


Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql- server-ver15



You have an Azure SQL database named db1 on a server named server1.

The Intelligent Insights diagnostics log identifies queries that cause performance issues due to tempDB contention.

You need to resolve the performance issues.

What should you do?

  1. Implement memory-optimized tables.
  2. Run the DBCC FLUSHPROCINDB command.
  3. Replace the sequential index keys with nonsequential keys.
  4. Run the DBCC DBREINDEX command.

Answer(s): A

Explanation:

TempDB contention troubleshooting:
The diagnostics log outputs tempDB contention details. You can use the information as the starting point for troubleshooting. There are two things you can pursue to alleviate this kind of contention and increase the throughput of the overall workload: You can stop using the temporary tables. You also can use memory- optimized tables.


Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/intelligent-insights-troubleshoot- performance#tempdb-contention



HOTSPOT (Drag and Drop is not supported)

You have an Azure subscription that contains an Azure SQL database.

The database fails to respond to queries in a timely manner.

You need to identify whether the issue relates to resource_semaphore waits.

How should you complete the Transact-SQL query? To answer, select the appropriate options in the answer area.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:






If your top wait type is RESOURCE_SEMAHPORE and you don't have a high CPU usage issue, you may have a memory grant waiting issue.

Determine if a RESOURCE_SEMAHPORE wait is a top wait
Use the following query to determine if a RESOURCE_SEMAHPORE wait is a top wait

SELECT wait_type,
SUM(wait_time) AS total_wait_time_ms
FROM sys.dm_exec_requests AS req
JOIN sys.dm_exec_sessions AS sess
ON req.session_id = sess.session_id
WHERE is_user_process = 1
GROUP BY wait_type
ORDER BY SUM(wait_time) DESC;


Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs



You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.

You scale up the virtual machine to 8 vCPUs and 64 GB of memory.

You need to reduce tempdb contention without negatively affecting server performance.

What is the number of secondary data files that you should configure for tempdb?

  1. 2
  2. 4
  3. 8
  4. 64

Answer(s): C

Explanation:

The number of secondary data files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files. Then if contention continues, increase the number of data files by multiples of four until the contention decreases to acceptable levels, or make changes to the workload/code.


Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database



You receive numerous alerts from Azure Monitor for an Azure SQL Database instance.

You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns for an extended period.

Which two actions should you perform? Each correct answer presents part of the solution.

Note: Each correct selection is worth one point.

  1. Set Threshold Sensitivity to High
  2. Set the Alert logic threshold to Dynamic
  3. Set the Alert logic threshold to Static
  4. Set Threshold Sensitivity to Low
  5. Set Force Plan to On

Answer(s): B,D

Explanation:

B: Dynamic Thresholds continuously learns the data of the metric series and tries to model it using a set of algorithms and methods. It detects patterns in the data such as seasonality (Hourly / Daily / Weekly), and is able to handle noisy metrics (such as machine CPU or memory) as well as metrics with low dispersion (such as availability and error rate).
D: Alert threshold sensitivity is a high-level concept that controls the amount of deviation from metric behavior required to trigger an alert.
Low The thresholds will be loose with more distance from metric series pattern. An alert rule will only trigger on large deviations, resulting in fewer alerts.
Incorrect Answers:
A: High The thresholds will be tight and close to the metric series pattern. An alert rule will be triggered on the smallest deviation, resulting in more alerts.


Reference:

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-dynamic-thresholds



Viewing page 38 of 85
Viewing questions 186 - 190 out of 470 questions


Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:

AI Tutor AI Tutor 👋 I’m here to help!