Consider a simple query, as simple as a select statement:

select * from Teachers where Name like N'%John%'

As soon as I start this query, it finishes. Thus, I get no chance to see sp_who2 to find out what type of lock this query has applied on database objects.

I can inflate this query with transaction statements:

begin transaction

select * from Teachers where Name like N'%John%'

-- Here, I won't commit transaction, thus holding the lock

But frankly, that doesn't seem the proper way for natural locking detection, because I've manipulated the default behavior using transaction statements.

Do we have a tool, like Display Estimated Execution Plan to show us some information about locking of a given query? If not, how can we find out what type of lock a given query can apply on a database, because they get executed TOO fast to be detected.

  • 1
    This is exactly how you would do it. You are not changing anything about the locking except the duration that the locks are held. May 18 '16 at 6:11
  • 1
    What database do you use? Please tag it.
    – Marco
    May 18 '16 at 6:59
  • @Marco, thanks for mentioning that. SQL Server. May 18 '16 at 9:15

No there's no way of knowing without running it.

The problem with opening a transaction is that you still won't see all the locks taken. At read committed the shared locks are released as soon as the row is read not end of the transaction. And even for locks that are held till end of transaction if a lock is escalated you miss the higher granularity locks taken out before that.

If this is a dev machine you can use trace flag 1200 (undocumented)

DBCC TRACEON(1200,3604,-1);
DBCC TRACEOFF(1200,3604,-1);

This will output the lock events to the messages tab, it is often best to execute the batch twice as the first run will contain lots of stuff related to compilation as well as the execution.

It is also possible to use SQL Server Profiler/Trace and extended events to see locks acquired and released.

Again this is best done on a dev server as these are potentially extremely frequent events and even with filtering would likely add a great deal of overhead on a busy server.

  • This is helpful. I wonder, why is the -1 global flag required? And do queries coming from outside of SSMS still take a performance hit while flag 1200 is active despite the 3604 print-to-console flag? Is that why you advise not to do this on a production database? Feb 23 '20 at 18:52
  • I don't know why the global flag is required but doesn't work at session scope. Yes SSMS isn't special in any way all queries will get the lock info sent back in the info messages. So likely large impact of that plus the fact that it is not documented anyway means save this for looking into things in a dev environment IMO Feb 23 '20 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.