Exam 70-762 topic 1 question 19 discussion

Actual exam question from Microsoft's 70-762

Question #: 19
Topic #: 1

You are developing an application that connects to a database.
The application runs the following jobs:

The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.
If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete.
You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA.
Which trace event class in the Locks event category should you use?

A. LockAcquired
B. LockCancel
C. LockDeadlock
D. LockEscalation

Show Suggested Answer

Suggested Answer: A 🗳️
The Lock:Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.
The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.

by Nickname17 at May 15, 2020, 2:31 a.m.

Comments

Submit Cancel

Alex5x

4 years, 5 months ago

I would go with the D.LockEscalation. This event class indicates that a finer-grained lock has been converted to a coarser-grained lock; for example, a row lock that is converted to a table lock. The problem here is that JobA delete more than 5000 rows at a time (10 million records). This causes lock escalation from a row to a table. This is because lock escalation is triggered when a single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table. (https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#lock-escalation) As a result, if JobA starts first, JobB needs to wait untill the table lock is released. In other words, we don't care about the fact that a row was locked. Since the records which are being deleted and the records being updated are different, this is not an issue. It becomes a problem only when JobA locks the table and we can see it when we use LockEscalation event class.

upvoted 3 times

Hoglet

4 years, 5 months ago

SQL doesn't have to escalate it's locking. It starts with the most appropriate level based what the optimiser "thinks". So it can just start with a Table Level Lock. LockAcquired is the best of the bunch here, but honestly you just wouldn't use any of the Lock events. I would use the "Blocking" event. Or just query the server when blocking was supposed to be occurring. sys.dm_exec_wait_tasks

upvoted 2 times

...

ASQL

4 years, 6 months ago

answer: LockAcquired

upvoted 2 times

...

Nickname17

4 years, 12 months ago

https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-cancel-event-class?view=sql-server-ver15

upvoted 1 times

Anette

4 years, 10 months ago

Cancel would stand only for JobB but the exercise requests locks acquires by JobA. I would go for LockAcquired or LockDeadlock, not sure which one

upvoted 1 times

Anette

4 years, 10 months ago

But since JobB is completed, means that there is no more deadlock but possibly resources from JobA were occupied, so I think its A correct. The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.

upvoted 9 times

...

Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 19 discussion

Comments

Alex5x

Hoglet

ASQL

Nickname17

Anette

Anette

SY0-701