SQL Server Sch-S locks on unrelated tables

Question

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?

Answer 1

Potential Causes

There are two likely reasons for this

1. There's a foreign key on the table
2. There's a trigger on the table

There are less likely reasons, too

1. Computed column based on a scalar function
2. 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.