Developing SQL Databases — Question 1

You are experiencing performance issues with the database server.
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.
What should you create?

Answer options

• A. a System Monitor report
• B. a sys.dm_exec_query_stats dynamic management view query
• C. a sys.dm_exec_session_wait_stats dynamicmanagement view query
• D. an Activity Monitor session in Microsoft SQL Management Studio.

Correct answer: C

Explanation

The correct answer is C because the sys.dm_exec_session_wait_stats dynamic management view provides insights into wait statistics for sessions, which can help diagnose issues related to locking and I/O contention. Option A, a System Monitor report, does not specifically target database locking or memory pressure. Option B focuses on query statistics rather than session waits, and option D, while useful for monitoring, does not provide the detailed wait statistics needed for the issues described.