Slow Queries with Linked Server sql 2012

• sharon-472085

  Ten Centuries

  Points: 1182

  More actions

  February 9, 2015 at 10:39 am

  #294598

  hi all,

  sql 2012 standard edititon.

  i have one primary view that contian 2 view lets say

  view 1 contain view a and view b :

  view a and view b contain same table sturcure ( same index ,column )

  view a point to tables on local server.

  view b point to tables on linked server.

  also i create synom that point to the primary view on the local server.

  problem is :

  when i do select from synom via proc seek opertion done which

  is expectd 🙂 on table in view a,

  but tables on view b ( on link server ) a scan oprtion done ....

  some point :

  1) tables in links server with no fragmention .

  2) same table sturcture. ( index , column).

  3) link server user is db owner. ( i try this 🙂

  4) with recompile didnt help.

  5) join in column type big int . ( exlude collation problem :))

  5) when i look at sql xml plan i see : index scan on all table in view via profiler on link server .

  at same time i see index seek on profiler on local server . (its mean same quary diffrent plan )

  6) tables on link server came for sql 2008 r2 ( dtach and atach to sql 2012)

  7) maybe synom is the issues.... ( but i check without synom same problem...)

  i will be happy to get any other suggestion link to articles and so on. ( i think

  most of search on google in last 8 hours was " slow performance link server")

  thanks alot

  sharon

  when proc accsess to table in view that point to local server

  the indexseek opretion is done which its good :).

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 9, 2015 at 11:02 am

  #1776458

  ahh, the pain of linked servers.

  linked servers don't belong in views; i'd consider finding out how much wiggle room you have on latency, and pull the data from the linked server periodically.

  then the view can pull from the locally staged data.

  remember, when you deal with linked servers, in this situation, here's what happens:

  if you join a linked server table to a local table, whether it is for select or update or whatever,here's what happens:

  most of the time the entire ate [LinkedServer]...TableName table is copied into tempdb.

  the joins to local tables are then performed behind the scenes to the temp table.

  the WHERE statement finally gets applied.

  the results are returned.

  so hidden from your expectation, even if you had a WHERE clause that would return one row from the linked server, the whole table can be pulled over, and then filtered.

  Instead, you want to use EXECUTE AT to avoid that situation, so the work is performed directly in the linked server engine

  echoed from an explanation i did a month or two ago for someone who was updating featuring a linked server:

  http://www.sqlservercentral.com/Forums/Topic1640834-1550-1.aspx#bm1640856

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!