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
SELECTquery 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.