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

[AntheaTung]

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:

1. Is MAXDOP = 1 the best setting for overall performance for all transactions?
2. 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

[MarceloMarques · Accepted solution]

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-2...

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-ma...

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-ma...

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-ma...

https://support.esri.com/en-us/search?cardtype=patches+and+updates&product=arcgis+server&s=Relevance...

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.

[AntheaTung]

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

[MarceloMarques]

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