Parallel Insert is not showing in MSSQL Stored Procedure execution Plan

Question

As you can see in the above screenshot, this query provides a parallel insert:

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  

However, as you can see in the above screenshot, this query doesn't do the parallel insert, and I'm wondering why:

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  

Answer 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
GO 
--query 1
GO
--query 2
GO
SET STATISTICS TIME OFF 
GO

Compare the CPU time and the elapsed time of the first and second queries.

Sometimes SQL Server decides against using a parallel insert to use less resources (less cpu time, for example) and the query ends up be slower (longer elapsed time).

If you have SQL Server 2016 or later you can try using the following undocument query hint to see if parallel execution is faster via OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE')) at the end of each query. Be aware that undocumented features like this should only be used in non-production environments and only for testing purposes, unless you're willing to assume the risk that the feature changes in future without notice, potentially breaking your code.

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