SQL Server 2016 SP 1 on HP 3PAR environment causes weird head blocker My client is on HP 3PAR hardware. Three servers are on different geographical locations, connected with a 40 gbit fibre connection. The SAN is SSD only, the machines have lots of cores.

The ETL process starts a single select query joining 2 tables. The execution plan shows the use of a non-clustered index combined with a heap scan for both tables.

This simple query becomes a head blocker for all other following queries, even those that are referencing completely different tables. 

My first guess is that something with the 3PAR could be involved, but I'm not really sure. Besides that, it's really weird that a 

select a.[value] from table a, inner join table b where table a.column = 12345 


manages to block 

update table c set column a = [value].


AFAIK there are no lock hints.

There are no triggers of foreign keys on the used tables. There are no functions or other creative extra's on the database.

I'm wondering if the HP 3PAR failover can in some way cause a query to lock a database or table or data partition or HOBT.
Any thoughts? If more info is needed, let me know. 

== Edit 12/28/2017
Answered the question :)
When you're troubleshooting a blocking chain happening live, start with sp_WhoIsActive:

sp_WhoIsActive @get_locks = 1


There's a blocked-by column that shows you who's blocking who. The lead blocker won't have anyone in their blocked-by column.

Once you've found the lead blocker, click on the locks column - it's an XML field that expands to show you the full list of locks held by that session.

It could be that the select is part of a longer transaction (there's also an open transactions column), or that the select isn't really what's blocking.

Your best bet for getting specific, actionable advice here would be to post a picture of the sp_WhoIsActive output, including the blocking columns, and then the contents of the XML for the locks.