cant find indexes when doing a remote query?/

Question

cant find indexes when doing a remote query?/

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
September 23, 2011 at 7:06 am

#390462

My batch of sql uses few synonyms pointing to a table on another server. Now when i run this query i see that 97% of the cost is on this remote query , if i copy the database local and run the same query cost is equally spread across. Is this the issue with the linked server or the indexes? I can try to post the exec plan , but is it like execution plan doesnt show up which indexes it is using on a remote server?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

That's one of the limits of plans. Anything done remotely comes back as one black box general step, usually with super high %.

The only way to debug is to run that part of the statement on the remote server. Tune that there. Then assume it's fine when you run it locally.

I say assume but I also check that if it takes 1 sec to run on the remote server that it still takes only 1 sec in the mixed server query.

I never had any super duper critical query in that situation so my research stopped there.

Hopefully Grant or Gail have more to offer on this matter.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

Ninja's_RGR'us (9/23/2011)
That's one of the limits of plans. Anything done remotely comes back as one black box general step, usually with super high %.
The only way to debug is to run that part of the statement on the remote server. Tune that there. Then assume it's fine when you run it locally.
I say assume but I also check that if it takes 1 sec to run on the remote server that it still takes only 1 sec in the mixed server query.
I never had any super duper critical query in that situation so my research stopped there.
Hopefully Grant or Gail have more to offer on this matter.

Makes sense. I actually copied all the databases locally on a test box and piece which was showed up as 97% usage in prod...is just now showing less than 4 %.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

For remote queries, to see what they are doing, you must look at those queries on the remote server as well as locally.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 4

You can't necessarily believe the cost basis of the things in the Execution Plan, anyway. The "cost" is one of those things known to lie especially if any "Hidden RBAR" comes into play.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 5

Jeff Moden (9/25/2011)
You can't necessarily believe the cost basis of the things in the Execution Plan, anyway. The "cost" is one of those things known to lie especially if any "Hidden RBAR" comes into play.

What is the best way then?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Trace to see the real total cost.

You can see it by statement & batches. And get the actual execution plan as well.