Environment two SQLServer 2000 boxes SQL_A (dual cpu) and SQL_B (quad cpu). In the same rack so there's no network routing issues between the two. SQL_A has a linked server setup to SQL_B. TCP/IP is the only protocol enabled on both servers. They have the same collation on both databases. Collation Compatible is checked on the linked server properties.
on SQL_A the SP1 does
Insert into #TempTable execute(@CMD)
The query returns 1500 rows, about 300kb total. It takes 160 seconds to execute. I can watch the CPU on SQL_B and it hovers around 25% for the 160 seconds. I can watch the network I/O on SQL_A and it's basically flatlined until the very end of SQL_B's CPU utilization. Which is what I expect as the result is transferred.
If I run the SP2 call directly on SQL_B the query executes in less than 2 seconds. When I do this the CPU does one tiny spike to about 30% for 1 second.
What the devil can becausing the execution to take 80 times longer when called from SQL_A?
