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.
You are a database administrator for a company that has an on-premises Microsoft SQL Server environment
and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each
customer uses a dedicated instance. The environments that you manage are shown in the following table.
You need to monitor WingDB and gather information for troubleshooting issues.
What should you use?
A.
sp_updatestats
B.
sp_lock
C.
sys.dm_os_waiting_tasks
D.
sys.dm_tran_active_snapshot_database_transactions
Explanation:
The sp_lock system stored procedure is packaged with SQL Server and will give you insight into the locks that
are happening on your system. This procedure returns much of its information from the syslock info in the
master database, which is a system table that contains information on all granted, converting, and waiting lock
requests.
Note: sp_lock will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new
development work, and plan to modify applications that currently use this feature. To obtain information about
locks in the SQL Server Database Engine, use the sys.dm_tran_locks dynamic management view.
sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2008and
later. Each row represents a currently active request to the lock manager for a lock that has been granted or is
waiting to be granted.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-locktransact-sql