0

I have a Azure SQL database, Premium P2: 250 DTUs, using 250GB of storage.

Sometimes even simple queries runs slow, for example by getting a single row from a simple 300mb table with with 500k rows, using a index seek on an (non cluster) index column.

I have one such example query that is run about 200k times per day. Most of the time it runs very quick (<10ms), but sometimes in the 100-1000ms range (which is an issue since we use it in a performance critical path with low latency requirements). Only a single query plan is used, and it looks like this: https://www.brentozar.com/pastetheplan/?id=Hk0OhNHK2

If I look at the DB load when this happens it looks fine, with less than 50DTUs used, less than 20% Data IO, and less than 20% CPU used..

However, if I look at this query in sys.query_store_wait_stats I get these stats for the last 24 hours:

  • Buffer IO: 37390ms
  • CPU: 1465ms
  • Memory: 351ms
  • Unknown: 153ms

My own guess is that our biggest tables (10s of millions of rows, 10-100GB data) in this DB are heavily used, and use GUIDs as primary key so many pages are read during a day since the data will be spread out. Could this mean that we are limited by amount of RAM so sometimes there's IO limitation even for the 300mb table above? Or maybe this is not relevant for this case..

Question: Is it normal to have this much Buffer IO waits? It feels quite a lot. And how can I diagnose and improve the situation?

Improve this question
4

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.