Parallel Insert is not showing in MSSQL Stored Procedure execution Plan

All we need is an easy explanation of the problem, so here it is.

Parallel Insert is not showing in MSSQL Stored Procedure execution Plan

IMAGE-1 ITS WORKING

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 [email protected]
WHERE SLD.isDeleted = 0 AND SLD.ID IS NOT NULL

Parallel Insert is not showing in MSSQL Stored Procedure execution Plan

IMAGE-2 ITS NOT WORKING

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  

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The queries obviously are not the same. In the first query you are trying to filter by inner join with… @ProjectID, in the second query you don’t.
Try using

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

and check what is the CPU time and the elapsed time of the first and second query.
Somethimes SQLServer avoids parallel inserts to use less resources(cpu time smaller) and the query ends up be slower (elapsed time bigger).

If you have SQL Server 2016 or later you could force the parallel execution with OPTION(USE HINT(‘ENABLE_PARALLEL_PLAN_PREFERENCE’)) at the end of each query.

For example:

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'));

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply