Administering a SQL Database Infrastructure — Question 252

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?

Answer options

• A. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
• B. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
• C. sys.sysprocesses, sys.dm_exec_query_text, sys.objects
• D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

Correct answer: D

Explanation

The correct answer is D because sys.dm_exec_requests provides information about the requests currently executing, sys.dm_exec_sessions gives details on user connections, and sys.dm_exec_query_text retrieves the text of the SQL queries being executed. Options A and C lack the necessary detail on executing queries or active sessions, while option B does not include sys.dm_exec_requests, which is essential for monitoring ongoing requests.