June 18, 2013 at 9:30 am
Hi SSC,
Question for you, when a user runs an INSERT INTO ... SELECT * FROM ... statement I've noticed that IOReads take place before really any decent amount of IOWrites take place.
What is SQL Server doing behind the scene? Is it loading the entire set of data pages from source table into memory before writing? I could see that being a problem if your buffer pool isn't larger than the table itself on disk...
Thanks in advance for the insight!
June 18, 2013 at 9:50 am
The physical insert will start occurring as soon as the Insert Operator in the plan has rows.
In some cases that will be pretty much immediate, but it needs to complete any blocking operators in the SELECT part of the plan prior to the first writes.
For example, if you're doing a large insert into a clustered index, there will likely be a sort operator in the plan to put the output of the SELECT into the order of the index.
A sort is a blocking operator and must be fully processed before it'll release rows to the next operator (in memory, or spilling to tempDB). There are also other blocking operators (e.g. Eager Spools etc.) which will effectively do the same.
Then there are things like hash join operators, which have to scan one of the inputs fully before it'll start releasing any rows, so, in short, there'll often be plenty of reads before you start getting any writes into the target table.
June 18, 2013 at 10:01 am
Ah cool, thanks for the explanation, I appreciate the response!
There is a clustered index on a single field for the destination table (and it's different than the clustered index of the source table) so based on what you shared it makes sense that its sorting records based on the order of the PK before doing this.
June 18, 2013 at 10:12 am
I just dug up the plan and sure enough you're correct HowardW!
This is the plan:
INSERT <---Clustered Index Insert <--- Sort <--- Compute Scalar <--- Compute Scalar <--- TOP <--- Clustered Index Scan
I should have mentioned the SELECT portion had 2 case statements which shows up in the Computer Scalar operations. Luckily they are very low in cost (10 and 0%) vs. the Sort at 28.8% and the 57.9% Clustered Index Insert
Very cool stuff, certainly my TIL (today I learned).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply