Is there a way to determine locks that a given query might apply on SQL objects? 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.
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.