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.

StackExchange Database Administrators Q#116115, answer score: 8