Exam 70-764 topic 1 question 26 discussion

Actual exam question from Microsoft's 70-764

Question #: 26
Topic #: 1

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
E. Activity Monitor

Show Suggested Answer

Suggested Answer: B 🗳️
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.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql

by Chaudhary at Nov. 28, 2019, 2:03 p.m.

Comments

Submit Cancel

Chandra111

4 years, 4 months ago

sys.dm_os_waiting_tasks

upvoted 1 times

...

Hoglet

4 years, 5 months ago

Sp_lock is the exam answer, but I would look at sys.dm_os_waiting_tasks WHERE session_id > 50, IRL, as this will show you what is waiting on what lock more easily than sp_lock

upvoted 3 times

...

TheSwedishGuy

5 years, 5 months ago

True, sys.dm_tran_locks is used by SQL Server 2019. SP Locks is still used for older SQL Server versions though.

upvoted 2 times

...

Chaudhary

5 years, 7 months ago

Missing Option: sys.dm_tran_locks This feature is in maintenance mode and may 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.

upvoted 2 times

...