Azure SQL Hyperscale DB is showing high CPU usage on certain days. What is best approach is diagnosing and fixing this?

Question

We have set up Azure SQL DB (Hyperscale: Gen5, 4 vCores) with single HA secondary replica. Some days we face severe performance issues. Jobs which are expected to finish by 8 am run till 2pm. Other days all seem to go well.

Affected days:
Compute utilization shows 70-80% AVG CPU usage and 30% Data IO% Avg usage. Database is using 800GB storage as of now. There are many scheduled jobs which run overnight. Performance issue causes them to run over extended period of time.

Performance setting:
Force Plan- ON
Create Index- ON
Drop Index- OFF

We run stat update fortnightly. There seem to be no performance recommendations shown in resource.

What can be done to diagnose the problem and also to put a temporary workaround in place?

Answer 1

You mentioned you update statistics but what about defragment indexes. Could you please try defragment indexes before running those jobs. If those jobs perform ETL tasks and update/delete/insert many rows please consider defragment indexes in the middle of that set of jobs. If the jobs perform ETL tasks consider also setting fill factor to 80 when defragment your indexes.

Use Ola Hallengren scripts to defragment indexes and update statistics at the same time.

Have you verified if there are missing indexes on that database associated to those jobs? You may need to create indexes needed.

Answer 2

Hi AlbertoMorillo,

Thank you for these details.

I will try scripts to defragment indexes and update statistics at the same time and let you know. Cheers.

Kind Regards,
Sagar