The reason i am looking for this information is last day when there was regular job runing whch usualy uses only 25% of CPU but it took 100% CPU instead...

Thanks stefan that gives more information for me. But i was wondering why would execution plan chose different route for the same query, LOOP join in the slower one and...

I think you can check the query from the query plan. But the thing is why is it running fast on larger database when compared to slow on smaller database....

Here is another thing i observed the slow query has a table spool which shows actual rows=1510403515 actual rewinds=46 but i dont see any table spool for fast query. how...

I am looking in a simple way... what ever may be the exceptions for remote queries when it is running faster for all other databases why only this database. Do...

I assume Table Spool is used to improve qry perofrmance, but looks like it is reverse in this scenarioa as it is used only in the slow running query.

from the query plan only difference i see is a Table Spool(Lazy Spool) in the slow query which i dont see in fast query, could some interpret that for me?

Lets see we go with your theory, as thayt is unseen at remote side how actually execution plan is used, what would be the best bet for me to fix...

I agree you point there a logic for it but actually my query is running faster for bigger database(remote) when compared to smaller one. Both databases are on different server...

Here is the situation in my area.

Slow Qry = Server A > View on Server B.Database B > View will access Server C

Fast Qry = Server A > View on...

most of our data is varchar, may be 90%

I understand it depends on that data, but i would like do some comparision which may not be accurate but give me an idea of hoe much disk space i...

Both queries i execute from the remote server.

Here is what i observed, there is an inner join in the query which joins remote server but if run this query locally on that server it is taking only...

Here is an example i just created, attached are the sql plans of slow and fast running query.

Please note that slowqry is running against 200gb database which takes 3 hrs...