High compute at 3 AM without any manually configured jobs

Question

Hi,

Recently I set up an Azure SQL database, with hardly any data in it. Current used space is 25 MB, so nothing much. However, every morning around 3 AM, I see CPU percentage going up, without any activity that I am aware of. I did not configure any back-up processes, but I do see that this activity costs around € 8 per day. Given that I'm using this SQL for a volunteering job, I'd like to keep costs at a minimum :-). How can I find out, what this activity is and how can I stop it?

Answer 1

One place to dig in is Query Store. Query Store records data about query execution aggregated per execution plan and run-time stats interval. The default interval is one hour.

The view sys.query_store_runtime_stats_interval lists the intervals. Beware of the time zone!

When you have an ID for an interval you want to investigate, you can query sys.query_store_runtime_stats for that id:

SELECT * FROM sys.query_store_runtime_stats WHERE runtime_stats_id = <id>
ORDER BY count_executions * avg_cpu_time DESC

The ORDER BY sorts the plans by total CPU time in the chosen interval.

The plan_id column brings you to sys.query_store_plan which gives you a query_id which takes you to sys.query_store_query. Here you find a query_text_id, which points to sys.query_store_text where you can find the actual query text.