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.