We are using SQL Azure Premium P1 which comes with 8GB ram and 150 IOPS. For a couple of hours, we have been seeing timeouts on SQL queries (even fairly simple queries take time).
In logs for SQL Azure we have seen that our physical read IOPS has increased from a steady 5 IOPS to around 130 in a matter of minutes but our actual production in the system relying on SQL Azure has not increased.
The minute it jumped to 130 IOPS the log shows that the database memory usage decreased from a very steady 4.8GB to around 1GB
I think it makes sense that If memory usage drops, physical reads increase.
I have looked at queries being executed on the server but see nothing out of the ordinary except for the slower response.
My question is whether there any reasons why SQL Azure (or SQL server for that matter) would drop things from memory and favor disk IO, even if there is plenty memory available.
Best Answer
Turns out that even if you pay for reserved resurces and are guaranteed 8GB of RAM, you aren't really guaranteed it. And it's not really reserved for you. In our case it turned out that another Windows Azure customer was using too much resources and our database got 1GB instead of 8.