Practice Exam

Explanations for each answer:

• sys.dm_exec_connections is incorrect. The sys.dm_exec_connections DMV provides information about network connections to the warehouse, including connection ID and network protocol details. However, it requires special permissions (only Admins can execute it), and it's not the primary view for identifying user names of active query sessions. Members, Contributors, and Viewers don't have permission to execute this view.
• sys.dm_exec_requests is incorrect. The sys.dm_exec_requests DMV shows information about actively running requests, including request ID, start time, status, and query details. While useful for monitoring query execution, it doesn't directly show user names. You would need to join it with sys.dm_exec_sessions to get the login_name information.
• queryinsights.long_running_queries is incorrect. The queryinsights.long_running_queries view provides historical information about queries that took a long time to execute. It's useful for performance analysis and identifying slow queries, but it shows historical data rather than currently active sessions and their user names in real-time.
• queryinsights.frequently_run_queries is incorrect. The queryinsights.frequently_run_queries view shows historical information about queries that are executed often in the warehouse. This is useful for identifying common query patterns for optimization, but it doesn't provide real-time information about users who are actively running queries right now.
• sys.dm_exec_sessions is correct. The sys.dm_exec_sessions DMV is the correct choice for identifying user names of active sessions. It includes the login_name column that shows user identification for each session, along with information such as client program name, client login time, current session settings, and session status. Members, Contributors, and Viewers can execute this DMV to see their own sessions, while Admins can see all sessions in the warehouse.