Consider a simple query, as simple as a
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.