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.
Answer(s): B,D
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.
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?
Answer(s): A
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.
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:
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 waitUse the following query to determine if a RESOURCE_SEMAHPORE wait is a top waitSELECT wait_type,SUM(wait_time) AS total_wait_time_msFROM sys.dm_exec_requests AS reqJOIN sys.dm_exec_sessions AS sessON req.session_id = sess.session_idWHERE is_user_process = 1GROUP BY wait_typeORDER BY SUM(wait_time) DESC;
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?
Answer(s): C
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.
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.
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.
https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-dynamic-thresholds
Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:
Our website is free, but we have to fight against AI bots and content theft. We're sorry for the inconvenience caused by these security measures. You can access the rest of the DP-300 content, but please register or login to continue.