Very high values on Avg DTU percentage usage

Hi Miguel,

is this an IFS Managed Cloud Instance or customer own Azure Subscription? What’s the Sizing of the DB? Did the volumes significantly changed since the last Sizing exercise?

Best regards
Roman

Hi @roklde , I tis not an IFS Managed Cloud. 

Volumes have not changed significantly since last sizing exercise and we are unsure why some queries are using such heavy data IO and taking all available DTU's. It's customers own Azure Subscription, we are using a Standard S3: 100 DTU's, SQL Database, with Azure SQL Server 

250GB of storage and only 1.5GB used

Here is disk space used as a percentage

Here is disk space used as a number, it's looking steady no huge growth or strange changes

Many thanks for looking into this 

Kind regards

Miguel

Thanks for providing the details!
Did you deploy the HTM into a separate Azure SQL DB Server? Can you check the Top CPU queries inside Azure Portal?

Best regards
Roman

Hi @roklde ,  HTM and PSO databases are in two different servers. Please see below top queries, many thanks for your advice. 

(@inOrganisationId int,@inDatasetId nvarchar(32),@inTypeId int,@inMinId int,@inMaxId int,@inDeleteFlag bit)SELECT [plan_id],[resource_id],[shift_id],[route_margin],[average_travel_time],[average_travel_distance],[total_allocations],[utilisation],[shift_pattern_id],[shift_start_datetime],[shift_end_datetime],[shift_overtime_end],[snap_time],[shift_actual_start],[shift_actual_end],[total_travel_time],[total_on_site_time],[total_break_time],[total_private_time],[total_unutilised_time],[vehicle_id]FROM (SELECT [delete_flag],[plan_id],[resource_id],[shift_id],[route_margin],[average_travel_time],[average_travel_distance],[total_allocations],[utilisation],[shift_pattern_id],[shift_start_datetime],[shift_end_datetime],[shift_overtime_end],[snap_time],[shift_actual_start],[shift_actual_end],[total_travel_time],[total_on_site_time],[total_break_time],[total_private_time],[total_unutilised_time],[vehicle_id],RANK() OVER (PARTITION BY [organisation_id],[dataset_id],[resource_id],[shift_start_datetime]ORDER BY [plan_id] DESC, [delete_flag] ASC) AS [id_rank]FROM [dbo].[Plan_Route]WHERE [organisation_id] = @inOrganisationId AND [dataset_id] = @inDatasetIdAND [allocation_type] = @inTypeIdAND [plan_id] BETWEEN @inMinId AND @inMaxId) AS VPWHERE [id_rank] = 1 AND [delete_flag] = @inDeleteFlag

(@inOrganisationId int,@inDatasetId nvarchar(32),@inTypeId int,@inMinId int,@inMaxId int,@inDeleteFlag bit)SELECT [schedule_exception_id],[schedule_exception_type_id],[plan_id],[resource_id],[activity_id],[visit_id],[label],[label_message_id],[sequence],[value],[reason_type_id]FROM (SELECT [delete_flag],[schedule_exception_id],[schedule_exception_type_id],[plan_id],[resource_id],[activity_id],[visit_id],[label],[label_message_id],[sequence],[value],[reason_type_id],RANK() OVER (PARTITION BY [organisation_id],[dataset_id],[schedule_exception_id],[sequence]ORDER BY [plan_id] DESC, [delete_flag] ASC) AS [id_rank]FROM [dbo].[Schedule_Exception_Data]WHERE [organisation_id] = @inOrganisationId AND [dataset_id] = @inDatasetIdAND [allocation_type] = @inTypeIdAND [plan_id] BETWEEN @inMinId AND @inMaxId) AS VPWHERE [id_rank] = 1 AND [delete_flag] = @inDeleteFlag

(@inOrganisationId int,@inDatasetId nvarchar(32),@inTypeId int,@inMinId int,@inMaxId int,@inDeleteFlag bit)SELECT [activity_id],[resource_id],[visit_id],[activity_start],[activity_end],[visit_status],[plan_id],[visit_type],[date_time_fixed],[duration],[fixed_resource],[shift_id],[rank],[shift_pattern_id],[shift_start_datetime],[reason_type_id],[same_location],[allocation_source],[commit_sort_value],[date_time_earliest],[shift_type_break_id],[location_id],[end_location_id],[delay_date_time_earliest],[duration_overhead],[parallel_index]FROM (SELECT [delete_flag],[activity_id],[resource_id],[visit_id],[activity_start],[activity_end],[visit_status],[plan_id],[visit_type],[date_time_fixed],[duration],[fixed_resource],[shift_id],[rank],[shift_pattern_id],[shift_start_datetime],[reason_type_id],[same_location],[allocation_source],[commit_sort_value],[date_time_earliest],[shift_type_break_id],[location_id],[end_location_id],[delay_date_time_earliest],[duration_overhead],[parallel_index],RANK() OVER (PARTITION BY [organisation_id],[dataset_id],[activity_id],[visit_id]ORDER BY [plan_id] DESC, [delete_flag] ASC) AS [id_rank]FROM [dbo].[Allocation]WHERE [organisation_id] = @inOrganisationId AND [dataset_id] = @inDatasetIdAND [allocation_type] = @inTypeIdAND [plan_id] BETWEEN @inMinId AND @inMaxId) AS VPWHERE [id_rank] = 1 AND [delete_flag] = @inDeleteFlag

(@inOrganisationId int,@inDatasetId nvarchar(32),@inTypeId int,@inMinId int,@inMaxId int)SELECT [object_type_id],[object_pk1],[object_pk2],[object_pk3],[object_pk4],[object_pk_name1],[object_pk_name2],[object_pk_name3],[object_pk_name4],[plan_id]FROM (SELECT [object_type_id],[object_pk1],[object_pk2],[object_pk3],[object_pk4],[object_pk_name1],[object_pk_name2],[object_pk_name3],[object_pk_name4],[plan_id],RANK() OVER (PARTITION BY [organisation_id],[dataset_id],[object_type_id],[object_pk_name4],[object_pk_name3],[object_pk_name2],[object_pk_name1],[object_pk4],[object_pk3],[object_pk2],[object_pk1]ORDER BY [plan_id] DESC) AS [id_rank]FROM [dbo].[Plan_Deletion]WHERE [organisation_id] = @inOrganisationId AND [dataset_id] = @inDatasetIdAND [allocation_type] = @inTypeIdAND [plan_id] BETWEEN @inMinId AND @inMaxId) AS VPWHERE [id_rank] = 1

Hi Miguel,

thanks for providing the queries. Do you see any correlation between those queries and the high DTU percentage you faced?

However, by reviewing the PSO Architecture & Sizing Guide the following recommendation is given on Azure DB Tiers:

Eventually you want to consider going for a vCore Serverless DB, if the system load changed and more activities are processed?

Further, there is a note on Routing tables which you might want to review:
 

Lastly, you might want to check if there is a newer HTM version available for your HTMs in use? This may give you also better performance.

Best regards
Roman

Hi@roklde , sorry I have just seen your latest comments. We are still trying to determine the root cause of this issue. We will be soon modifying the DTU threshold/rebuilding indexes to review db performance. It might be that we need to increase specs of the environment. Many thanks for your recommendations. I will keep you inform of progress.

Best regards

Miguel

Hi @MIGDNA  have you managed to resolve it?

hi ​@MIGDNA  did you resolve it?

S3 is the minimum DTU level we suggest for production systems. Maybe it’s not enough. 

It’s worthwhile to upgrade to 6.15 as well because there are performance improvements in between.

But adding custom indexes is not a good idea.

So having the minimum should be OK. What did change in 6.15 that can help?

Last ​@MIGDNA did you manage to find the root cause and/or resolve it?

Having the minimum is okay only if PSO environment sizing activity on your scheduling problem decided so.

There were several performance improvements in major versions and bug fixes in patch versions in between which may help. You can check the PSO documentation for this which should be available in IFS Community already. Anyways, it’s better to be in latest versions because then investigating and fixing issues will be easier.