How do I prevent a massive SELECT from blocking other statements? Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production and here's what sys.dm_exec_requests was showing for that period of time... The longest running query was that heavy SELECT which had PAGEIOLATCH_SH wait type. Next were other queries - most often INSERT statements which had PAGEIOLATCH_EX wait type, all running for dozens of seconds instead of completing promptly. So basically that heavy SELECT alone disrupts other queries.

How do I resolve this? I'm okay with heavy SELECT running slowly but other queries should not be disrupted.
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.