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
I agree with A: the duration column will show how long JobB needed to wait before it could acquire a lock on the table.
https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-acquired-event-class?view=sql-server-ver15
Lock escalation is of course the mechanism responsible for JobA to get a table lock but this event does not tell you anything about JobB.