DP-300 practice questions 171–180

1. Question 171 · 354 in bank

   Select 2

   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.

   • AEnable query optimizer fixes.
   • BEnable Resource Governor.
   • CEnable parameter sniffing.
   • DCreate a workload group.
   • EConfigure In-memory OLTP.
   • FRun the Database Engine Tuning Advisor.
   • GReduce the Max Degree of Parallelism value.

   Show answer

   Answer: BD

   Explanation

   Answer is B and D from what I understood. From Microsoft docs: Resource Governance To enforce resource limits, Azure SQL Database uses a resource governance implementation that is based on SQL Server Resource Governor, modified and extended to run in the cloud. In SQL Database, multiple resource pools and workload groups, with resource limits set at both pool and group levels, provide a balanced Database-as-a-Service. User workload and internal workloads are classified into separate resource pools and workload groups. User workload on the primary and readable secondary replicas, including geo-replicas, is classified into the SloSharedPool1 resource pool and UserPrimaryGroup.DBId[N] workload groups, where [N] stands for the database ID value. In addition, there are multiple resource pools and workload groups for various internal workloads.

   Source: Adapted from community discussion — smaa

   Next question

2. Question 172 · 354 in bank

   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?

   • AImplement memory-optimized tables.
   • BRun the DBCC FLUSHPROCINDB command.
   • CReplace the sequential index keys with nonsequential keys.
   • DRun the DBCC DBREINDEX command.

   Show answer

   Answer: A

   Explanation

   TempDB contention 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.

   Source: Adapted from community discussion — eric0718

   Next question

3. Question 173 · 354 in bank

   HOTSPOT - You have an Azure SQL database named db1. You need to retrieve the resource usage of db1 from the last week. How should you complete the statement? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: sys.resource_stats - sys.resource_stats returns CPU usage and storage data for an Azure SQL Database. It has database_name and start_time columns. Box 2: DateAdd - The following example returns all databases that are averaging at least 80% of compute utilization over the last one week. DECLARE @s datetime; DECLARE @e datetime; SET @s= DateAdd(d,-7,GetUTCDate()); SET @e= GETUTCDATE(); SELECT database_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization FROM sys.resource_stats - WHERE start_time BETWEEN @s AND @e GROUP BY database_name - HAVING AVG(avg_cpu_percent) >= 80 Incorrect Answers: sys.dm_exec_requests: sys.dm_exec_requests returns information about each request that is executing in SQL Server. It does not have a column named database_name. sys.dm_db_resource_stats: sys.dm_db_resource_stats does not have any start_time column. Note: sys.dm_db_resource_stats returns CPU, I/O, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. Historical data is maintained for approximately one hour. Sys.dm_user_db_resource_governance returns actual configuration and capacity settings used by resource governance mechanisms in the current database or elastic pool. It does not have any start_time column. Reference: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database

   Source: Adapted from community discussion — vicky007_87

   References

   • Microsoft Learn
   • Microsoft Learn

   

   Next question

4. Question 174 · 354 in bank

   HOTSPOT - 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:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   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

   

   Next question

5. Question 175 · 354 in bank

   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?

   • A2
   • B4
   • C8
   • D64

   Show answer

   Answer: B

   Explanation

   I think the correct answer is B), because you should have 8 data files for 8vCPU in total, but you already have some data files and you just need to add/configure extra secondary files

   Source: Adapted from community discussion — dryd

   Next question

6. Question 176 · 354 in bank

   Select 2

   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.

   • ASet Threshold Sensitivity to High
   • BSet the Alert logic threshold to Dynamic
   • CSet the Alert logic threshold to Static
   • DSet Threshold Sensitivity to Low
   • ESet Force Plan to On

   Show answer

   Answer: BD

   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

   Source: Adapted from community discussion — Aggie0702

   References

   • Microsoft Learn

   Next question

7. Question 177 · 354 in bank

   You have an Azure SQL database named sqldb1. You need to minimize the amount of space by the data and log files of sqldb1. What should you run?

   • ADBCC SHRINKDATABASE
   • Bsp_clean_db_free_space
   • Csp_clean_db_file_free_space
   • DDBCC SHRINKFILE

   Show answer

   Answer: A

   Explanation

   DBCC SHRINKDATABASE shrinks the size of the data and log files in the specified database. Incorrect Answers: D: To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command. Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql

   Source: Adapted from community discussion — kkkiet

   References

   • Microsoft Learn

   Next question

8. Question 178 · 354 in bank

   You have an Azure SQL Database server named sqlsrv1 that hosts 10 Azure SQL databases. The databases perform slower than expected. You need to identify whether the performance issue relates to the use of tempdb by Azure SQL databases in sqlsrv1. What should you do?

   • ARun Query Store-based queries
   • BReview information provided by SQL Server Profiler-based traces
   • CReview information provided by Query Performance Insight
   • DRun dynamic management view-based queries

   Show answer

   Answer: D

   Explanation

   Just checked my Azure Database, "Query Performance Insight" only provides CPU, Data IO, Log IO, Duration, and Execution count information. Answer C is not correct.

   Source: Adapted from community discussion — U_C

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

9. Question 179 · 354 in bank

   DRAG DROP - You are building an Azure virtual machine. You allocate two 1-TiB, P30 premium storage disks to the virtual machine. Each disk provides 5,000 IOPS. You plan to migrate an on-premises instance of Microsoft SQL Server to the virtual machine. The instance has a database that contains a 1.2-TiB data file. The database requires 10,000 IOPS. You need to configure storage for the virtual machine to support the database. Which three objects should you create in sequence? To answer, move the appropriate objects from the list of objects to the answer area and arrange them in the correct order. Select and Place:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Follow these same steps to create striped virtual disk: ✑ Create Log Storage Pool. ✑ Create Virtual Disk ✑ Create Volume Box 1: a storage pool - Box 2: a virtual disk that uses stripe layout Disk Striping: Use multiple disks and stripe them together to get a combined higher IOPS and Throughput limit. The combined limit per VM should be higher than the combined limits of attached premium disks. Box 3: a volume - Reference: https://hanu.com/hanu-how-to-striping-of-disks-for-azure-sql-server/

   Source: Adapted from community discussion — reachmymind

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   

   Next question

10. Question 180 · 354 in bank

    HOTSPOT - You need to use an Azure Resource Manager (ARM) template to deploy an Azure virtual machine that will host a Microsoft SQL Server instance. The solution must maximize disk I/O performance for the SQL Server database and log files. How should you complete the template? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

    

    This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

    Show the answer figure

    Answer: See figure below

    Explanation

    I think it should be opposite, i.e. Read-Only and None. See https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage?view=azuresql#caching We need to set 'Read-Only' caching for data disks and 'None' for the log disk. There is a loop (copy) in the ARM template, it evaluates whether current disk ID is greater than sum of data disks. If yes then it's the log disk, so assign None, otherwise the value in the variable (i.e. Read-Only). Similar example is here: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/create-sql-vm-resource-manager-template

    Source: Adapted from community discussion — licna

    References

    • Microsoft Learn
    • Microsoft Learn

    

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 171. 354 questions available.