Very high (99%) insertion cost in estimated execution plan I have four tables with 500,000 to 4,000,000 rows. The script joins them and insert into a temporary table with some where clause. It is taking long. The execution plan shows 99% cost only for the insert whereas there are multiple scans in the plan. Snapshot of the related part of the execution plan is shown in the image. The drive is solid state. How do I interpret the plan. SQL Server execution plan
The high relative cost for the insert is a result of the cost-based model used by the optimizer.  In particular, it looks like SQL Server assigns a much higher I/O cost to inserts into a temp table compared to reading rows from a heap or clustered index.

The percentages you see are purely based on these cost estimates.

As a simplified example, check out this execution plan, which uses the Stack Overflow database:

SELECT * 
INTO #tempusers
FROM dbo.Users;




I'm inserting all of the rows from the dbo.Users table (about 300,000) into a temp table.

The differences in estimated cost between the index scan and the table insert operators is dominated by "Estimated I/O Cost:"


Clustered Index Scan


Estimated rows: 299,611
Estimated I/O cost: 5.42312

Table Insert


Estimated rows: 299,611
Estimated I/O cost: 1,104.13



The estimated I/O cost for the temp table insert is two hundred times higher than the estimated I/O cost of the index scan.

Looking at the actual execution plan, you can see that the scan took 386 ms, and the insert took 514 ms.  The insert, in practice, was definitely not 200 times more "expensive" by any measure.



All of this is to say that, in this case in particular, don't focus on those percentages.  The I/O cost estimates are a bit outdated, and were originally based on systems with spinning disk storage and smaller amounts of RAM.  

If you want help with specific parts of the query in your screenshot, you should post a new question that includes the actual execution plan.  I see a few sorts in the screenshot, which can often be avoided with indexes.  You also mentioned that what is inserted into the temp table is filtered - indexes can help with that as well.