I have a 9 million record table that is about 452MB in size (disregarding NC indexes). Below is physical stats and the columns
As a simple example, if I dump the table into a temp table, the actual plan shows a whopping 42GB estimated and actual data size. See image below.
I suspect this is due to the two varchar(4000) columns which is causing the estimated row size to be larger than they are. However, I really don't think 42GB are being transfered - not sure how that would be possible.. I don't know if I'm seeing performance issues because of this but the query plan looks scary because of these wrong estimates. Why is the query plan so wrong on the estimated and actual table scans?