Inconsistent Linked Server Execution Plans

I am witnessing something odd with regard to execution plans and a
linked server.
I have a restore of a production database in two different
environments, one DEV the other QA. This database resides on the linked
server in our setup. The other database -- the local database -- has
been installed and populated by a script in both DEV and QA. The
version and edition of SQL Server are identical in both environments.
In the DEV environment, executing a query which uses the linked server
(in a four part name) results in a reasonable execution plan with
filtering ocurring on the remote database before the rows are returned.
However, in the QA environment the same query results in an execution
plan that has _all_ of the rows from the remote tables returned before
filtering. The tables in the database on the linked server are somewhat
large > 1M. The execution times differ by a vast amount.
I'm really at a loss. I know the query optimizer is a fickle mistress
but is this evidence of something predictable and knowable? What are
the types of things that I might take a look at in order to discover
why the same query in two very similar environments generates such
wildly different execution plans? And, more than that why on earth
would the query optimizer not just as a matter of course _always_
filter the rows remotely before returning them? Is there any way to
force the query optimizer to filter the rows remotely before returning
them? I've looked at the openquery function which does this but is not
practical in this circumstance given the query itself. Rearchitecture
is an option but I'm dying for some kind of explanation for this
behavior. My brain is broke.Hi
I am not familiar with your problem in particular, but you could try
updating indexes and statistics on the linked server to see if anything
changes. To avoid any possible filtering problem you could execute a remote
stored procedure or possibly use OPENQUERY.
John
"person" wrote:
> I am witnessing something odd with regard to execution plans and a
> linked server.
> I have a restore of a production database in two different
> environments, one DEV the other QA. This database resides on the linked
> server in our setup. The other database -- the local database -- has
> been installed and populated by a script in both DEV and QA. The
> version and edition of SQL Server are identical in both environments.
> In the DEV environment, executing a query which uses the linked server
> (in a four part name) results in a reasonable execution plan with
> filtering ocurring on the remote database before the rows are returned.
> However, in the QA environment the same query results in an execution
> plan that has _all_ of the rows from the remote tables returned before
> filtering. The tables in the database on the linked server are somewhat
> large > 1M. The execution times differ by a vast amount.
> I'm really at a loss. I know the query optimizer is a fickle mistress
> but is this evidence of something predictable and knowable? What are
> the types of things that I might take a look at in order to discover
> why the same query in two very similar environments generates such
> wildly different execution plans? And, more than that why on earth
> would the query optimizer not just as a matter of course _always_
> filter the rows remotely before returning them? Is there any way to
> force the query optimizer to filter the rows remotely before returning
> them? I've looked at the openquery function which does this but is not
> practical in this circumstance given the query itself. Rearchitecture
> is an option but I'm dying for some kind of explanation for this
> behavior. My brain is broke.
>|||I updated the statistics on all the databases involved to no avail. I'm
unable to use the OPENQEURY function since I have to join tables across
the databases.
Which execution plan seems to be the anomaly? Would it be the execution
plan where the data is filtered on the linked server prior to being
returned or the execution plan that returns all the rows before
filtering?|||I think I may have found some promising information:
http://www.sql-server-performance.com/linked_server.asp
/quote
When running distributed queries on a linked server, if the linked
server has the same character set and sort order (collation) as the
local SQL Server, then you can reduce overhead and boost performance if
you set the SP_SERVEROPTION "collation compatible" option to true. What
this setting does is tell SQL Server to assume that all columns and
character sets on the remote server are compatible with the local
server. This same option can also be turned on for a linked server
using Enterprise Manager
If this option is not selected, then the distributed query being
executed on the remote server must return the entire table to the local
server in order for the WHERE clause to be applied. As you can imagine,
this could potentially return a lot of unnecessary data over the
network, slowing it down.
If the option is selected, (which is always recommended if the
collations are the same on both servers), then the WHERE clause is
applied on the remote server. This, of course, means that much less
data is transmitted over the network, often greatly speeding up the
distributed query. [7.0, 2000] Updated 2-16-2004
/endquote|||Hi
Also check out the other information at the bottom of the article.
Some examples of locally performed operations include:
Data conversion operations
Queries that use the bit, timestamp, or uniqueidentifier data types
Queries that use the TOP clause
INSERTS, UPDATES, or DELETES
John
"person" wrote:
> I think I may have found some promising information:
> http://www.sql-server-performance.com/linked_server.asp
> /quote
> When running distributed queries on a linked server, if the linked
> server has the same character set and sort order (collation) as the
> local SQL Server, then you can reduce overhead and boost performance if
> you set the SP_SERVEROPTION "collation compatible" option to true. What
> this setting does is tell SQL Server to assume that all columns and
> character sets on the remote server are compatible with the local
> server. This same option can also be turned on for a linked server
> using Enterprise Manager
> If this option is not selected, then the distributed query being
> executed on the remote server must return the entire table to the local
> server in order for the WHERE clause to be applied. As you can imagine,
> this could potentially return a lot of unnecessary data over the
> network, slowing it down.
> If the option is selected, (which is always recommended if the
> collations are the same on both servers), then the WHERE clause is
> applied on the remote server. This, of course, means that much less
> data is transmitted over the network, often greatly speeding up the
> distributed query. [7.0, 2000] Updated 2-16-2004
> /endquote
>|||The collation compatibility was not the magic bullet I was hoping it
would be. The two database use the same collation and enabling the
collation compatibility option on the linked server had no effect.
However, digging in further it now appears that there is a correlation
between W2003 and the poor linked server performance.
In our initial setup in the dev environment the local server was a W2K
box while the remote server was a W2003 box. In the QA environment both
servers are W2003. I installed the local db on W2003 box in the Dev
environment. To my surprise, the problem is now reproducible. I can
reproduce the problem in the dev environment only when the local db is
on a W2003 box but not when it is on a W2K box.
So there is some correlation between W2003 and poor linked server
performance.|||Hi
A pure guess would be the check out DTC is working correctly and possibly
re-install it. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306843
John
"person" wrote:
> The collation compatibility was not the magic bullet I was hoping it
> would be. The two database use the same collation and enabling the
> collation compatibility option on the linked server had no effect.
> However, digging in further it now appears that there is a correlation
> between W2003 and the poor linked server performance.
> In our initial setup in the dev environment the local server was a W2K
> box while the remote server was a W2003 box. In the QA environment both
> servers are W2003. I installed the local db on W2003 box in the Dev
> environment. To my surprise, the problem is now reproducible. I can
> reproduce the problem in the dev environment only when the local db is
> on a W2003 box but not when it is on a W2K box.
> So there is some correlation between W2003 and poor linked server
> performance.
>|||I'm not sure that that article applies to my situation. We're not using
distributed transactions.
Does this seem like a situation where a support ticket to Microsoft
might be advisable? I can consistently demonstrate that the query
optimizer devises radically different execution plans for the same
stored procedure depending on which operating system the local db is on
(all other factors being equal -- or apparently so). I don't know if
anybody from Microsoft looks at this forum...but would starting a new
thread with a more specific subject line get this problem any better
exposure?|||Hi
It is not something I have come across. I could not find anything at
http://lab.msdn.microsoft.com/productfeedback so raising it with PSS may be
an idea.
John
"person" wrote:
> I'm not sure that that article applies to my situation. We're not using
> distributed transactions.
> Does this seem like a situation where a support ticket to Microsoft
> might be advisable? I can consistently demonstrate that the query
> optimizer devises radically different execution plans for the same
> stored procedure depending on which operating system the local db is on
> (all other factors being equal -- or apparently so). I don't know if
> anybody from Microsoft looks at this forum...but would starting a new
> thread with a more specific subject line get this problem any better
> exposure?
>|||Did you come up with an explanation for the differences in the way the
two OSs handled the queries?
I'm curious, because I migrated from a perfectly-good W2K installation
to a W2003 machine, and my OLE DB Sybase linked server threw ALL KINDS
of errors... things that look to me like collation differences.
Rick|||Rick,
No real explanation yet as to why the OSs handle the queries
differently.
We put in a support ticket with Microsoft. Their tech said to enable
the dynamic parameters option for the Sql Server provider. So in other
words, drop the linked server and recreate it with the dynamic
parameters option selected. I tried that and it had no effect. The
Microsoft tech then suggested we reboot the machine. I did and it
appears to have had a good effect. I am still in the process of testing
and I will post an update if anything interesting ocurrs. Here is the
knowledge base article the tech provided as documentation for his
suggestion:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q320208.
The article doesn't mention the reboot step. But it is a Microsoft
product and well...
Interestingly enough, the Sql Server provider option for the dynamic
parameters is _not_ selected on the w2k machine. The execution plan on
the w2003 machine is still not as efficient as on the w2k machine but
I'm closer than where I was last month.