Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question.
Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
Start of repeated scenario.
You are a database administrator for a company that has on-premises Microsoft SQL Server environment. There are two domains in separate forests. There are no trust relationships between the domains. The environment hosts several customer databases, and each customer uses a dedicated instance running SQL Server 2016 Standard edition. The customer environments are shown in the following table.
End of repeated scenario.
You need to monitor WingDB and gather information for troubleshooting issues.
Which two tools should you use? Each correct answer presents a complete solution.
A. sys.dm_tran_locks
B. sp_lock
C. sys.dm_tran_active_snapshot_database_transactions
D. Activity Monitor
E. sp_monitor
The performance issues is related to locking.
B: sp_lock reports snapshot information about locks, including the object ID, index ID, type of lock, and type or resource to which the lock applies.
D: The Activity Monitor in SQL Server Management Studio is useful for ad hoc views of current activity and graphically displays information about:
Processes running on an instance of SQL Server.
Blocked processes.
Locks.
User activity.
Incorrect Answers:
E: System Monitor primarily tracks resource usage, such as the number of buffer manager page requests in use, enabling you to monitor server performance and activity using predefined objects and counters or user-defined counters to monitor events. System Monitor (Performance Monitor in Microsoft Windows NT 4.0) collects counts and rates rather than data about the events (for example, memory usage, number of active transactions, number of blocked locks, or CPU activity). You can set thresholds on specific counters to generate alerts that notify operator
References: https://docs.microsoft.com/en-us/sql/relational-databases/performance/performance-monitoring-and-tuning-tools?view=sql-server-2017