SQL server database with ArcGIS Enterprise 10.9.1 - option with MAXDOP 1 when default SQL Instance MAXDOP = 8 and cost threshold for parallelism = 80
We are using Microsoft SQL Server 2019 (RTM-GDR) (KB5014356) - 15.0.2095.3 (X64) Apr 29 2022 18:00:13 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) and geodatabase details as below.
We have been reviewing our performance and noticed there are few long running queries as below. Our database is set for MAXDOP 8 and the cost threshold for parallelism = 80 . The cost of the long running queries is higher but does not seem to be using parallelism and using OPTION (MAXDOP 1). Do we need to make any configuration changes here ? We have installed all ArcGIS Enterprise patches.
@MarceloMarques : I saw another post on a related topic. So just tagging you here in case you have more information.
sql statements:
select OBJECTID, ASSETGROUP, ASSETTYPE, ASSOCIATIONSTATUS, ISCONNECTED, FROMDEVICETERMINAL, TODEVICETERMINAL, SHAPE, GLOBALID, CPSUBNETWORKNAME, SUPPORTEDSUBNETWORKNAME, SUPPORTINGSUBNETWORKNAME, SYSTEMSUBNETWORKNAME, PRESSURESUBNETWORKNAME, ISOLATIONSUBNETWORKNAME, MATERIAL, LIFECYCLESTATUS, NOMINALDIAMETER, OUTSIDEDIAMETER, ACTUALINTERNALDIAMETER, DIAMETERUNITS, MAOPDESIGN, SOPRANGE, PRESSUREUNITS, MAOPUNITS, COMMODITY, PROJECTNUMBER, JOBCODE, PROJECT, PROJECTSTARTDATE, PROJECTENDDATE, COMMONTRENCHCODE, RELAYCODE, REGULATORYTYPE, PINCHABLE, MEASUREDLENGTH, LENGTHSOURCE, LENGTHUNITS, OWNEDBY, MANUFACTURER, INSERVICEDATE, INSTALLDATE, INSTALLATIONMETHOD, PIPELINECODE, ALIGNMENT, BONDEDINSULATED, CPTRACEABILITY, CPOVERRIDE, LOCATIONDESCRIPTION, REASON, RETIREDDATE, DESIGNPRESSUREENTERED, DESIGNPRESSURESTANDARD, DESIGNPRESSUREUNITS, MAINTYPE, SERVICELINETYPE, ACTUALMATERIAL, WIREMATERIAL, WALLTHICKNESS, DEPTH, ORIGINALCOVERDEPTH, DEPTHUNITS, PRIMARYCOATINGTYPE, NOTES, CRITICALINDICATOR, WORKORDERID, LEGACYNETKEY, DISTLEVEL, LEGACYTAG, LEGACYFLTAG, CREATIONDATE, CREATOR, LASTUPDATE, UPDATEDBY, AUSTRALIANSTANDARD, PIPEROUGHNESS, EXTERNALDIAMETER, MATERIALTYPE, SDR, CARTOOFFSET, BLENDPERCENTAGE, CAST(0 AS FLOAT(53)), GISMASTER.GISDATA.PIPELINELINE.GDB_GEOMATTR_DATA from GISDATA.PIPELINELINE WHERE GISDATA.PIPELINELINE.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 GISDATA.PIPELINELINE WHERE OBJECTID IN (SELECT OBJECTID FROM GISDATA.PIPELINELINE WITH (INDEX(S778_idx)) WHERE PIPELINELINE.SHAPE.STIntersects(@P1) = 1 AND ("ASSETGROUP" in (2) and "LifecycleStatus" = 😎 AND GDB_BRANCH_ID = 0) AND GDB_BRANCH_ID = 0 AND GDB_FROM_DATE <= @P2 ) br__ WHERE br__.rn_ = 1 AND br__.GDB_IS_DELETE = 0 AND br__.SHAPE.STIntersects(@P3) = 1) AND ("ASSETGROUP" in (2) and "LifecycleStatus" = 😎 OPTION (MAXDOP 1)
----------------------------------------------------------------------------------------------------------------------------------------------
select GISMASTER.GISDATA.UN_8507_TOPOLOGY.OID, GISMASTER.GISDATA.UN_8507_TOPOLOGY.BLOBTABLEID, GISMASTER.GISDATA.UN_8507_TOPOLOGY.PAGENUMBER, GISMASTER.GISDATA.UN_8507_TOPOLOGY.PAGEBLOB from GISDATA.UN_8507_TOPOLOGY WITH (FORCESEEK INDEX(R1541_pk)) WHERE GISDATA.UN_8507_TOPOLOGY.GDB_ARCHIVE_OID IN (SELECT GDB_ARCHIVE_OID FROM (SELECT GDB_ARCHIVE_OID,ROW_NUMBER() OVER(PARTITION BY OID ORDER BY GDB_FROM_DATE DESC) rn_, GDB_IS_DELETE FROM GISDATA.UN_8507_TOPOLOGY WHERE GDB_BRANCH_ID = 0 AND GDB_FROM_DATE <= @P1 AND OID IN (SELECT DISTINCT OID FROM GISDATA.UN_8507_TOPOLOGY WHERE (BLOBTABLEID = @P2 AND PAGENUMBER = @P3) AND GDB_BRANCH_ID = 0) ) br__ WHERE br__.rn_ = 1 AND br__.GDB_IS_DELETE = 0) AND (BLOBTABLEID = @P4 AND PAGENUMBER = @P5) OPTION (MAXDOP 1)
---------------------------------------------------------------------------------------------------------------------------------------------------
select GISMASTER.GISDATA.UN_8507_EDGEWEIGHTS.OID, GISMASTER.GISDATA.UN_8507_EDGEWEIGHTS.BLOBTABLEID, GISMASTER.GISDATA.UN_8507_EDGEWEIGHTS.PAGENUMBER, GISMASTER.GISDATA.UN_8507_EDGEWEIGHTS.PAGEBLOB from GISDATA.UN_8507_EDGEWEIGHTS WITH (FORCESEEK INDEX(R1543_pk)) WHERE GISDATA.UN_8507_EDGEWEIGHTS.GDB_ARCHIVE_OID IN (SELECT GDB_ARCHIVE_OID FROM (SELECT GDB_ARCHIVE_OID,ROW_NUMBER() OVER(PARTITION BY OID ORDER BY GDB_FROM_DATE DESC) rn_, GDB_IS_DELETE FROM GISDATA.UN_8507_EDGEWEIGHTS WHERE ((GDB_BRANCH_ID = 0 AND GDB_FROM_DATE <= @P1) OR (GDB_BRANCH_ID = @P2 AND GDB_FROM_DATE <= @P3)) AND OID IN (SELECT DISTINCT OID FROM GISDATA.UN_8507_EDGEWEIGHTS WHERE (BLOBTABLEID = @P4 AND PAGENUMBER = @P5) AND GDB_BRANCH_ID IN (0, @P6)) ) br__ WHERE br__.rn_ = 1 AND br__.GDB_IS_DELETE = 0) AND (BLOBTABLEID = @P7 AND PAGENUMBER = @P8) OPTION (MAXDOP 1)
