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: 

(@P1varbinary(max),@P2datetime2(7),@P3varbinary(max))select  SHAPE,  SUBNETWORKNAME,  OBJECTID,xyz_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

MMBR

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.