Why is my Azure SQL (SQL Server) database getting overloaded with data IO for periods at a time? 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 (&lt; 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_who2 and 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.
Given that during the spike(s) your log activity is minimal we can assume there isn't any (or much) DUI going on.

You mention at one point that the spike doesn't affect performance, and at another that it does. Which is it? 

You also mention that this goes away after a scale operation. This makes sense as it's analogous to an on-premise restart which will effectively kill all processes etc.

Do I assume correctly in guessing that this database is being accessed from the application-tier? If so, I suspect that your connections are not being closed properly. The garbage collector is supposed to take care of these eventually (which shouldn't be relied upon), but I've seen this exact situation occur because of unclosed connections from the app-tier. In our case, the application was so busy that we eventually received concurrent connection errors which is what lead us to the problem.

Try the following query during the spike:

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    s.status,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
ORDER BY c.connect_time ASC


If I'm correct, you'll find a whole bunch of records returned with the status of Sleeping, or worse Running. If that's the case you have even bigger problems in the app-tier. 

We can further debug this by copying the database, using the following query (using basic tier to avoid excessive costs), and monitoring for this behaviour.

CREATE DATABASE Database1_copy AS COPY OF Database1 ( EDITION = 'basic' );