I am running an Azure SQL database under the S2 edition (50 DTUs). Normal use of the server usually hangs around 10% DTU. However, this server regularly gets into a state where it will send DTU usage of the database to 85-90% for hours. Then all of a sudden it goes back down to the normal 10% usage.
Queries against the server from the application still seem to be operating quickly during this overloaded state.
I can scale the server from S2 => anything (S3 for example) => S2 and it seems to clear out whatever state it is hung in. But then a few hours later it will again repeat the same overloaded state cycle. Another weird thing I've noticed is that if I run this server on an S3 plan (100 DTU) 24/7 I have not observed this behavior. It only seems to occur when I have downscaled the database to an S2 plan (50 DTU). On the S3 plan I am always sitting at 5-10% DTU usage. Obviously underutilized.
I've checked into Azure SQL query reports looking for rogue queries, but I don't really see anything unusual and it shows my queries using resources as I would expect.
As we can see here though, the usage is all coming from Data IO. If I change the performance report here to show top Data IO queries by MAX, we see this:
Looking at these long running quires seems to point to statistics updates. Not really anything running from my application. For example, query 16302 there shows:
SELECT StatMan([SC0], [SC1], [SC2], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [SC1], [SC2], step_direction([SC0]) over (order by NULL) AS [SB0000] FROM (SELECT [UserId] AS [SC0], [OrganizationId] AS [SC1], [Id] AS [SC2] FROM [dbo].[Cipher] TABLESAMPLE SYSTEM (1.250395e+000 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SC1], [SC2], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 16)
But then again, the report also shows that these queries are only using a small percentage of the Data IO usage on the server (< 4%). I also run statistics updates (and index rebuilds) on the entire database on a weekly basis as part of its regular maintenance.
Here is another report that shows MAX data IO queries for a timespan that covers several hours only during the high-resource-usage incident.
As we can see, not really any queries reporting significant data IO usage.
I have also ran
sp_whoisacive on the database and do not really see anything jumping out at me (though I will admit I am not an expert with these tools).
How do I figure out what is going on here? I don't think any of my application queries are to blame for this resource usage and I get the feeling that there is some internal process running in the background on the server that is killing it.