I have someone who retrieves data from a linked server to a new table as following:
* newly edited - turns out there's multiple queries executed *
select * into dbDestination.dbo.tableDestination from server2.dbSource.dbo.tableSource select * into dbDestination.dbo.tableDestination2 from server2.dbSource.dbo.tableSoure2 select * from dbDestination.dbo.tableDestination3 from server2.dbSource.dbo.tableSource3
(don't ask me why - I just want to know why the effects are as they are)
tableSource - 24million rows (3,5GB data)
tableSource2 - 26million rows (8GB data)
tableSource3 - 1million rows (300MB data)
This query seems to effectively make my tempdb on "server2" to grow out of control and results in the disk being full. Like it is dumping 50GB data in there!?
These statements are NOT seperated by a "GO" command. Does that make it run simultaneously? Or do the second and third statements wait until the first one is complete? I was thinking maybe tempdb can not send so much across the wire at once - resulting in tempdb spill? But why would it spill to tempdb at all? I have no sorts or joins or anything.
Looking at my execution plan I see nothing special: SELECT INTO (0%) <- Table Insert (36%) <- Top (0%) <- Compute Scalar (0%) <- Remote Query (64%)
What happens within this "remote query" ? Is it spilling so much data to the tempdb? Anyone who could explain why this is happening? It seems weird. I would have expected maybe tempdb to be affected on the server where the request is being issued, but not on the remote.
Thanks in advance for any help to solve this mystery.