SQL Server Sch-S locks on unrelated tables I am a bit confused. I myself am MCSE and thought I would understand most parts of the SQL Server.

Now I have a stored procedure here, doing inserts and updates on two tables dbo.tabA and dbo.tabB. Let's call this SPID 1.

On a complete different table sales.invoices I am recreating a clustered index on a partition scheme. Let's call this SPID 2.

To my surprise SPID 1 is blocked by SPID 2.

Looking into sys.dm_tran_locks, it tells me that SPID 1 is waiting for a Sch-S lock on sales.invoices (OBJECT LOCK) but  sales.invoices is neither mentioned directly in the SPID 2 query nor is it part of any view or function.

Why is SPID 1 trying to achieve a lock on an object that is totally untouched by its query?
Potential Causes

There are two likely reasons for this


There's a foreign key on the table
There's a trigger on the table


There are less likely reasons, too


Computed column based on a scalar function
Indexed view maintenance


There's also the possibility that the blocking is due to metadata changing, where modifications to system views can block each other.

Figuring It Out

You can use sp_WhoIsActive. 

If you run it like so: sp_WhoIsActive @get_locks = 1;, it should help you get to the root of your blocking issue.