which server the heavy lifting of the optimization and processing of the SELECT statement will be done in the following scenario? In other words, if the SELECT statement were a complex, memory hungry query, which server will it kill? ServerA or ServerB? ServerA has a linked server to ServerB Connect to ServerA and execute the following SQL: INSERT INTO #temp1 SELECT F1, SUM(F2) FROM ServerB.Database1.Table1 Group by F1
You cannot create a temp table remotely using the linked server. It pulls data locally and creates the temp table locally too. So in your scenario, Server A will get the load of the temp table. Your network will get slammed passing all the data from B to A and Server B will get the load of the Select statement.