You have an application that uses a Microsoft SQL Server database on a Microsoft Azure virtual machine. The application experiences performance issues, which you suspect are related to the…

Question

Options

• Asys.dm_os_memory_pools
• Bsys.dm_exec_requests
• Csys.dm_exec_connections
• Dsys.dm_exec_sessions

• A
  16% (9)
• B
  4% (2)
• C
  7% (4)
• D
  73% (40)

Explanation

Explanation

sys.dm_exec_sessions (Option D) is the correct choice because it provides detailed information about all active user sessions, including connection pooling-related attributes such as the number of active sessions, their states, and resource consumption - making it the ideal DMV to investigate performance degradation tied to high concurrent connection counts exceeding 150.

Why the distractors are wrong:

• Option A (sys.dm_os_memory_pools) focuses on SQL Server's internal memory pool allocations, which is unrelated to connection pooling diagnostics.
• Option B (sys.dm_exec_requests) shows currently executing requests/queries in real time, but doesn't give you a holistic picture of session and pooling behavior.
• Option C (sys.dm_exec_connections) tracks physical network connection details (like protocol and packet info), but lacks the session-level pooling context needed to diagnose this issue.

Memory Tip: Think "S for Sessions = S for Scalability problems" - when you need to investigate how many users/sessions are hitting your database and causing performance issues at scale, sys.dm_exec_**sessions** is your go-to DMV. If the question mentions concurrent users or connection thresholds, think sessions first.

No community discussion yet for this question.