HOTSPOT
-
You have an Azure Synapse Analytics dedicated SQL pool.
You need to monitor the database for long-running queries and identify which queries are waiting on resources.
Which dynamic management view should you use for each requirement? To answer, select the appropriate options in the answer area.
NOTE: Each correct answer is worth one point.
The sys.dm_pdw_lock_waits view is specific to SQL Server and is used to monitor lock waits and lock resources in regular SQL Server environments, not in Azure Synapse Analytics dedicated SQL pools. My answers are: 1. sys.dm_pdw_exec_requests 2. sys.dm_pdw_waits There is a similar question in the microsoft official practice assessment and the explaination is the following: The sys.dm_pdw_waits view holds information about all wait stats encountered during the execution of a request or query, including locks and waits on a transmission queue
Its dm_pwd_waits: Queries in the Suspended state can be queued due to a large number of active running queries. These queries also appear in the sys.dm_pdw_waits waits query with a type of UserConcurrencyResourceTyp from the official learning path: https://learn.microsoft.com/en-us/training/modules/manage-monitor-data-warehouse-activities-azure-synapse-analytics/6-use-dynamic-management-views-to-identify-troubleshoot-query-performance
1. sys.dm_pdw_exec_requests 2. sys.dm_pdw_waits
correct https://learn.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs?view=azuresql
box 1: is correct box 2: sys.dm_pdw_waits https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-waits-transact-sql?view=aps-pdw-2016-au7
1. sys.dm_pdw_exec_requests This DMV provides information about all active queries in the database, including their query ID, status, execution time, and resource utilization. You can use this DMV to identify long-running queries by filtering for queries that have been running for a long period of time. 2. sys.dm_pdw_waits This DMV provides information about the wait stats encountered by active queries. You can use this DMV to identify queries that are waiting for resources, such as CPU, memory, or I/O.
"Queries in the Suspended state can be queued due to a large number of active running queries. These queries also appear in the sys.dm_pdw_waits waits query with a type of UserConcurrencyResourceType."
explained here : https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor 1. sys.dm_pdw_exec_requests 2. sys.dm_pdw_waits
1. sys.dm_pdw_sql_requests It is more precise about the request made in the question. Please notice that exec_requests contains also all the informations from sql_requests, which is a subset of exec_requests. But we're only interested in SQL queries performances, so even sql_requests is OK. 2. sys.dm_pdw_lock_waits we're specifically referring to time spent in waiting a resource lock, so this is the correct table. It is supported by dedicated SQL pool, but not by serverless.
provided answers are correct