How do I figure out what is causing Data IO spikes on my Azure SQL database?

user_1090359 0
Viewed 5731

I have a Azure SQL production database that runs at around 10-20% DTU usage on average, however, I get DTU spikes that take it upwards of 100% at times. Here is a sample from the past 1 hour:

enter image description here

I realize this could be a rouge query, so I switched over to the Query Performance Insight tab, and I find the following from the past 24 hours:

enter image description here

This chart makes sense with regards to the CPU usage line. Query 3780 takes the majority of at CPU, as expected with my application. The Overall DTU (red) line seems to follow this correctly (minus the spikes).

However, in the DTU Components charts I can see large Data IO spikes occurring that coincide with the Overall DTU spikes. Switching over to the TOP 5 queries by Data IO, I see the following:

enter image description here

This seems to indicate that there are no queries that are using high amounts of Data IO.

How do I find out where this Data IO usage is coming from?

Finally, I see that there is this one, "odd ball" query (7966) listed under the TOP 5 queries by Data IO with only 5 executions. Selecting it shows the following:

enter image description here

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], [Type] AS [SC1], [Id] AS [SC2] FROM [dbo].[Cipher] TABLESAMPLE SYSTEM (1.828756e+000 PERCENT)
    WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER
ORDER BY [SC0], [SC1], [SC2], [SB0000] ) AS _MS_UPDSTATS_TBL 
OPTION (MAXDOP 16)

What is this query?

This does not look like any query that my application has created/uses. The timestamps on the details chart seem to line up with the approximate times of the overall Data IO spikes (just prior to 6am) which leads me to think this query has something to do with all of this.

Are there any other tools can I use to help isolate this issue?

azure-sql-database azure sql-server
2 Answers

The query is updating statistics..this occurs when this setting AUTO UPDATE STATISTICS is on..This should be kept on and you can't turn it off..this is a best practice..

You should update stats manually only when when you see a query not performing well and stats are off for that query..

Also below are some rules when SQL will update stats automatically for you

  • When a table with no rows gets a row
  • When 500 rows are changed to a table that is less than 500 rows
  • When 20% + 500 are changed in a table greater than 500 rows

By ‘change’ we mean if a row is inserted, updated or deleted. So, yes, even the automatically-created statistics get updated and maintained as the data changes.There were some changes to these rules in recent versions and sql can update stats more often

References:
https://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/

user_29753960

It seems that query is part of the automatic update of statistics process. To mitigate the impact of this process on production you can regularly update statistics and indexes using runbooks as explained here. Run sp_updatestats to immediately try to mitigate the impact of this process.

user_81841390
Related