topic Re: Azure SQL Database with ArcGIS Enterprise 10.9.1 - Query option with MAXDOP 1 when default SQL Instance MAXDOP = 8 in ArcGIS Enterprise in the cloud Questions

Azure SQL Database with ArcGIS Enterprise 10.9.1 - Query option with MAXDOP 1 when default SQL Instance MAXDOP = 8

AntheaTung — Tue, 29 Aug 2023 20:42:04 GMT

Hello,

We are using Azure SQL Database with ArcGIS Enterprise 10.9.1.

In Azure Portal, we noted in the Database Performance Overview, there are some long running queries. Picked one of the long running queries and here is the SQL:

(@P1 varbinary(max),@P2 datetime2(7),@P3 varbinary(max))select SHAPE, SUBNETWORKNAME, OBJECTID,XXX_NETWORK.UNADMIN.ELECTRICSUBNETLINE.GDB_GEOMATTR_DATA from UNADMIN.ELECTRICSUBNETLINE WITH (FORCESEEK INDEX(R50_pk)) WHERE UNADMIN.ELECTRICSUBNETLINE.GDB_ARCHIVE_OID IN (SELECT GDB_ARCHIVE_OID FROM (SELECT GDB_ARCHIVE_OID,SHAPE,ROW_NUMBER() OVER(PARTITION BY OBJECTID ORDER BY GDB_FROM_DATE DESC) rn_, GDB_IS_DELETE FROM UNADMIN.ELECTRICSUBNETLINE WHERE OBJECTID IN (SELECT OBJECTID FROM UNADMIN.ELECTRICSUBNETLINE WITH (INDEX(S20_idx)) WHERE ELECTRICSUBNETLINE.SHAPE.Filter(@P1) = 1 ) AND GDB_BRANCH_ID = 0 AND GDB_FROM_DATE <= @P2 ) br__ WHERE br__.rn_ = 1 AND br__.GDB_IS_DELETE = 0 AND br__.SHAPE.Filter(@P3) = 1) OPTION (RECOMPILE, MAXDOP 1)

By default Azure SQL Database is set to use MAXDOP 8. The Query above is recompiled with MAXDOP = 1. The above Query is coming from ArcGIS Pro usingbranch versioning via REST service.

Questions:

Is MAXDOP = 1 the best setting for overall performance for all transactions?
Where is the Query hint with Recompile option injected from? In ArcGIS Pro? In REST API? or in a stored procedure in ArcSDE?

Thank you!

Anthea

Re: Azure SQL Database with ArcGIS Enterprise 10.9.1 - Query option with MAXDOP 1 when default SQL Instance MAXDOP = 8

MarceloMarques — Tue, 29 Aug 2023 21:44:28 GMT

Hello @AntheaTung ,

BUG-000151624 - Add MAXDOP 1 to all branch versioned queries that do not have a spatial or attribute-provided query filter.

"Disabling parallelism reduces contention on cpu resources is not intended for OLTP style queries which should perform well when executed in a serialized fashion."

The fix for this Bug was first introduced in Nov/09/2022, see list of cumulative Patches below.

ArcGIS Server Map and Feature Service Security 2023 Update 1 Patch - Aug/22/2023
https://support.esri.com/en-us/patches-updates/2023/arcgis-server-map-and-feature-service-security-2023-update-1-patch

ArcGIS Server 10.9.1 Utility Network and Data Management Patch 5 - July/07/2023
https://support.esri.com/en-us/patches-updates/2023/arcgis-server-10-9-1-utility-network-and-data-management-patch-5

ArcGIS Server 10.9.1 Utility Network and Data Management Patch 4 - Feb/27/2023
https://support.esri.com/en-us/patches-updates/2023/arcgis-server-10-9-1-utility-network-and-data-management-patch-4-8089

ArcGIS Server 10.9.1 Utility Network and Data Management Patch 3 - Nov/09/2022
https://support.esri.com/en-us/patches-updates/2022/arcgis-server-10-9-1-utility-network-and-data-managemen-8077

https://support.esri.com/en-us/search?cardtype=patches+and+updates&product=arcgis+server&s=Relevance&version=10%252E9%252E1&product=arcgis+utility+network

Please, if you are experiencing performance issues with ArcGIS Enterprise 10.9.1 and Azure SQL Database ArcGIS Geodatabase 10.9.1 then open a ticket with Esri Technical Support to investigate the problem further.

I hope this clarifies your question.

Re: Azure SQL Database with ArcGIS Enterprise 10.9.1 - Query option with MAXDOP 1 when default SQL Instance MAXDOP = 8

AntheaTung — Wed, 30 Aug 2023 13:14:30 GMT

Hi @MarceloMarques thanks you so much for the helpful information above ! The above Query was from Prod and we have ArcGIS ServerUN patch 3 applied. It does not look like the patch is resolving the MAXDOP issue for us.

Will log a ticket with Support.

Anthea

Re: Azure SQL Database with ArcGIS Enterprise 10.9.1 - Query option with MAXDOP 1 when default SQL Instance MAXDOP = 8

MarceloMarques — Wed, 30 Aug 2023 15:19:09 GMT

Hi AntheaTung, I am glad to assist. Thanks, Marcelo 🙂