Could you use Sql profiler to capture exactly what query caused the time out?
Hi Quan, thanks for picking up interest. Unfortunately I didn't find option to export or save log from Azure Data Studio profiler, but here are screenshots and info.
When it fails, it just after mdpsp_sys_DeleteMetaFieldFromMetaClass is logged as rpc_completed and attention shows up in next row.
This is duration in printscreen whith another field. It is usually microsecond value between 30 and 31 seconds.
This is how profiling log looks like when removing field succeeds. Duration is below some 19 seconds.
Two behaviours that I cannot get control of are:
If you want to run it as one-time thing, then the setting is here
https://vimvq1987.com/episerver-commerce-commandtimeout-configuration/
Yes. After extending commandTimeout on SqlDataProvider configuration I was able to detach field from meta class. First to 300, wasn't enough. Then to 600 sec and it worked. It of course timed out web request so commerce app showed 503, but still finished DB task in the background.
Thanks to team Quan Mai for help!
As a side note, the time out probably comes from this query
DELETE FROM CatalogContentProperty WHERE MetaFieldId = @MetaFieldId AND MetaClassId = @MetaClassId
DELETE FROM ecfVersionProperty WHERE MetaFieldId = @MetaFieldId AND MetaClassId = @MetaClassId
So you can add a non clustered index on (MetaFieldId, MetaClassId) on both tables to make the query runs faster, then drop them :)
Hi all,
I am trying to detach (uncheck) meta field from a Variant in Episerver Commerce Manager.
1) With local SQL Server and catalog with some 40k entries it works without any problems.
2) In Azure against SQL in Elastic Poll (200 DTU) it fails with "[SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.]" That catalog has approx 120k entries.
I have tried to extend connection timeout, but that has no effect. The execution pumps up DTUs to 100% and then fails.
I have tried with 300 DTU, and then with 800 DTU. Same error after some 30 seconds.
Does this problem ring any bells for any of you guys? Why is this save of the meta class ramping up DB so high? Also the period when I get Execution Timeout looks like another timeout setting somewhere, that I did not find. But still, it wouldn't be of many help as this is how resources go, increase after increase of DTU.
Consumed DTU as we kept increasing it.
No matter is it 200, 300 or 800 DTU database maxes to 100% and then execution timeout happens.
Version of Commerce is 13.16.0