3

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.

3
  • 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. Commented May 18, 2016 at 6:11
  • 1
    What database do you use? Please tag it.
    – Marco
    Commented May 18, 2016 at 6:59
  • @Marco, thanks for mentioning that. SQL Server. Commented May 18, 2016 at 9:15

1 Answer 1

8

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);
SELECT ....
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.

2
  • 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? Commented Feb 23, 2020 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 Commented Feb 23, 2020 at 18:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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