April 6, 2012 at 1:31 pm
If I run a procedure against the physical server in management studio, it executes in 0 - 1 seconds - Great! It has a decent execution plan, I'm happy.
If I execute the exact same thing from a different server (via linked server) it executes in 0 - 1 seconds - Great! It has a decent execution plan, I'm happy. ExampleEXEC [SERVER].DB.SCHEMA.PROCEDURE @Param1
If I run the exact same procedure from the linked server, and it's physically compiled there and calls the tables from the linked server - it goes out to lunchSELECT col1, col2, col3
FROM [LINKED SERVER].[DB].[SCHEMA].
WHERE this = @Param1
And there are a lot of PAGEIOLATCH_SH's occuring on the linked server (that aren't there when the code runs locally on the server???)
My question isn't about the procedure itself (so not posting any DDL or a query plan), it's about whether or not others have experienced similar issues running things via a linked server. If so, what it might be? Is there a Fix? SOL?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 6, 2012 at 1:39 pm
When using four-part naming query uses to transfer whole tables over the network from remote to local server, then apply filtering in local server - that's why it takes much longer.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 6, 2012 at 1:50 pm
PaulB-TheOneAndOnly (4/6/2012)
When using four-part naming query uses to transfer whole tables over the network from remote to local server, then apply filtering in local server - that's why it takes much longer.
+1
Fully agree with Paul's assessment.
April 6, 2012 at 9:25 pm
Alternative:
EXECUTE...AT
http://msdn.microsoft.com/en-us/library/ms188332(v=sql.105).aspx
See example G. This way we can force a select to execute on the remote server.
If you have a table ready that matches the shape of the SELECT you can use the INSERT...EXEC technique to capture the results in a table on the local server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply