7

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.

  • It seems to be possible to run the select under a user with different priority stackoverflow.com/a/65439 but no idea if it would help in your case. – jkavalik Sep 25 '15 at 9:01
  • What does your execution plan look like? There may be ways that the query can be improved to reduce IO and therefore reduce blocking. – Mark Sinkinson Sep 25 '15 at 9:15
  • @MarkSinkinson I cannot even obtain an execution plan anymore because that requires running the query and disrupting production. – sharptooth Sep 25 '15 at 11:10
  • 4
    Can you not backup to a test server and run it there? – Mark Sinkinson Sep 25 '15 at 11:15
  • 1
    @jkavalik That would be a speculation, not a real plan. – sharptooth Sep 25 '15 at 11:33
8

Your main options are:

  1. Tune the SELECT query so it uses fewer I/O resources.
  2. Run the query at a quiet time.
  3. Run the query on a separate copy of the database (e.g. a readable secondary).
  4. Run the query in an I/O-limited resource pool as described here*.
  5. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.