Which dynamic management view (DMV) should be used to diagnose long-running report queries?

The choice of using sys.dm_exec_requests to diagnose long-running report queries is grounded in its specific purpose within SQL Server. This dynamic management view provides detailed information about the currently executing requests on the server. It yields critical data such as the status of requests, the time they have been running, and the command being executed, all of which are essential in pinpointing which queries are taking longer than expected to execute.

When diagnosing performance issues, particularly for long-running queries, sys.dm_exec_requests is invaluable because it allows you to directly observe the execution state of each request. You can see which sessions are processing queries, track their resource usage, and identify bottlenecks in query execution. This insight is crucial for optimizing query performance and ensuring efficient system operation.

In contrast, other options like sys.dm_exec_sessions primarily provide information about user sessions and their state, which may not specifically indicate the performance of individual queries. sys.dm_exec_connections reveals details about network connections, and sys.dm_pdw_exec_requests is specific to Parallel Data Warehouse environments, making them less relevant for general diagnosis of long-running report queries. Thus, the focused functionality of sys.dm_exec_requests makes it the most appropriate choice for diagnosing issues related to long-running report queries.