Exam DP-300 topic 7 question 14 discussion

Actual exam question from Microsoft's DP-300

Question #: 14
Topic #: 7

HOTSPOT -
You have an Azure SQL database.
You need to identify whether a delayed query execution is associated to a RESOURCE_SEMAPHORE wait.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer:

Box 1: wait_type -
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;

Box 2: sys.dm_exec_requests -
Use the sys.dm_exec_requests or sys.dm_os_waiting_tasks to see the wait_type and wait_time.
Azure SQL RESOURCE_SEMAPHORE wait "wait_time" is_user_process

by wyindualizer at Nov. 13, 2022, 10:32 a.m.

Comments

Submit Cancel

TheDataGuy

Highly Voted 11 months, 1 week ago

FYI, the answer is correct however the Group By is incorrect, it should be wait_type not TARGET1 unless in the select you add the alias.

upvoted 5 times

...

wyindualizer

Most Recent 1 year ago

Correct https://www.sqlshack.com/sql-server-performance-tuning-resource_semaphore-waits/

upvoted 4 times

...