How do I prevent a massive SELECT from blocking other statements?
Your main options are:


Tune the SELECT query so it uses fewer I/O resources.
Run the query at a quiet time.
Run the query on a separate copy of the database (e.g. a readable secondary).
Run the query in an I/O-limited resource pool as described here*.
Move to a size/tier with higher I/O capacity.


Side note: This answer does not mention using isolation levels like RCSI or SI. Those apply to lock blocking, and the question contains no reference to that: PAGEIOLATCH_XX waits are associated with waits on physical I/O.



*This feature may not be available in Azure SQL Database.