2

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

Execution Plan

  • 1
    Hi and welcome to dba.SE. With only a screenshot it is hard to say for sure, posting the plan to Pastetheplan.com would be more helpful. However, it looks like you are doing straight table scans, and sorting the data. Adding indexes to remove the sorts should help. If you would post the tables and the query, then we could assist with that aswell. Also, Heap tables (the one with the table scans) are like a dirty room, you can throw everything in (insert) easily, but getting stuff out (sorting , selecting) is gonna be harder. You could Consider adding clustered indexes if possible. – Randi Vertongen Jan 23 at 8:39
1

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;

high cost insert

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.