MSSQL 儲存過程執行計劃中未顯示並行插入

圖 1 它的工作原理

INSERT INTO #StateAllocationData WITH (TABLOCK) (ProjectID,StateId,StateLineDescriptionId,PartnerID,Value)

SELECT @ProjectID as ProjectID,sld.StateId,sld.ID,TaxReturnPartnerNumber,0 as Value

FROM Meta.States S(NOLOCK)

LEFT JOIN Meta。 StateAllocationLineDescriptions SLD(NOLOCK) ON S.StateId = SLD.StateId

join Ottp.PartnerData PD on ProjectID=@Projectid

WHERE SLD.isDeleted = 0 AND SLD.ID IS NOT NULL

圖像 2 它不工作

INSERT INTO  #SAmt WITH (TABLOCK) (ProjectID,StateId,K1SummaryID,StateLineDescriptionId)  
SELECT @ProjectID AS 'ProjectID',  
S.StateId,  
SLD.StateLineDescriptionId AS 'K1SummaryID',  
SLD.ID AS 'StateLineDescriptionId'  
FROM Meta.States S(NOLOCK)  
LEFT JOIN Meta.StateAllocationLineDescriptions SLD(NOLOCK) ON S.StateId = SLD.StateId  
WHERE --SLD.isK1Summary <> 0 and  
 SLD.isDeleted = 0  
AND SLD.ID IS NOT NULL  

查詢顯然不一樣。在第一個查詢中，您嘗試通過內部連接過濾… @ProjectID，在第二個查詢中您沒有。嘗試使用

SET STATISTICS TIME ON 
--query 1
--query 2
SET STATISTICS TIME OFF 

並檢查第一個和第二個查詢的 CPU 時間和經過的時間。Somethimes SQLServer 避免並行插入以使用更少的資源（cpu 時間更小）並且查詢最終變得更慢（經過的時間更大）。

如果您有 SQL Server 2016 或更高版本，則可以在每個查詢結束時使用 OPTION(USE HINT(‘ENABLE_PARALLEL_PLAN_PREFERENCE’)) 強制並行執行。

例如：

INSERT INTO #SAmt WITH (TABLOCK)
(
   ProjectID,
   StateId,
   K1SummaryID,
   StateLineDescriptionId
)
SELECT @ProjectID AS 'ProjectID',
      S.StateId,
      SLD.StateLineDescriptionId AS 'K1SummaryID',
      SLD.ID AS 'StateLineDescriptionId'
FROM Meta.States S (NOLOCK)
   LEFT JOIN Meta.StateAllocationLineDescriptions SLD (NOLOCK)
       ON S.StateId = SLD.StateId
WHERE --SLD.isK1Summary <> 0 and  
   SLD.isDeleted = 0
   AND SLD.ID IS NOT NULL
OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

引用自：https://dba.stackexchange.com/questions/314959