nerdexam
Microsoft

70-465 · Question #127

You administer a SQL Server 2014 instance. Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues. You want to…

The correct answer is D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text. This question tests your ability to identify the correct DMVs to monitor SQL Server connections, blocking, active queries, and restore progress. The correct combination uses sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_sql_text (referred to here as…

Submitted by obi.ng· Mar 5, 2026Design and implement database solutions for SQL Server

Question

You administer a SQL Server 2014 instance. Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues. You want to write a query of the system views that will report the following: - Number of users that have a connection to the server - Whether a user's connection is active - Whether any connections are blocked - What queries are being executed - Whether the database restore is still executing and, if it is, what percentage of the restore is complete. Which system objects should you use in your query to best achieve this task?

Options

  • Asys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
  • Bsys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
  • Csys.sysprocesses, sys.dm_exec_query_text, sys.objects
  • Dsys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

How the community answered

(28 responses)
  • A
    11% (3)
  • B
    4% (1)
  • C
    7% (2)
  • D
    79% (22)

Why each option

This question tests your ability to identify the correct DMVs to monitor SQL Server connections, blocking, active queries, and restore progress. The correct combination uses sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_sql_text (referred to here as sys.dm_exec_query_text).

Asys.dm_exec_requests, sys.dm_exec_sessions, sys.objects

sys.objects is a catalog view for database schema objects (tables, procedures, etc.) and provides no information about active sessions, blocking, query text, or restore progress, making it irrelevant to this monitoring task.

Bsys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects

sys.dm_exec_query_stats contains historical aggregate performance statistics for cached query plans and does not show currently executing requests, blocking status, or restore percent_complete, so it cannot fulfill the real-time monitoring requirements; sys.objects is also irrelevant here.

Csys.sysprocesses, sys.dm_exec_query_text, sys.objects

sys.sysprocesses is a deprecated compatibility view that lacks the percent_complete column needed to report restore progress, and sys.objects again provides no session or request monitoring data, making this combination insufficient for the task.

Dsys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_textCorrect

sys.dm_exec_sessions provides session-level data including number of connected users and whether sessions are active, sys.dm_exec_requests provides request-level data including blocking information (blocking_session_id), percent_complete for operations like RESTORE DATABASE, and the sql_handle needed to retrieve query text, and sys.dm_exec_sql_text (query_text) retrieves the actual T-SQL being executed. Together these three objects satisfy all five reporting requirements specified in the question.

Concept tested: SQL Server DMVs for session and request monitoring

Source: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql

Topics

#SQL Server monitoring#dynamic management views#active connections#blocked sessions

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice