Good morning,
There is a stored procedure. It uses linked server. As we will be migrating to amazon cloud, our architect instructed not to replace linked server with openquery. Please find the execution plan details attached. Please help.
Technology Tips and News
Good morning,
There is a stored procedure. It uses linked server. As we will be migrating to amazon cloud, our architect instructed not to replace linked server with openquery. Please find the execution plan details attached. Please help.
Hi Chaitanya,
I am assuming that you are referring to numbers collected from an execution plan. If so, these inaccurate estimates are usually caused by missing or out of date statistics on the tables/columns in question.
Can you run
UPDATE STATISTICS tablename
on each table in the query?
If that doesn't help you may need to add some indexes to the tables, which will create statistics automatically.
Also do you have the 'auto create statistics' and 'auto update statistics' options turned on for the database? Generally it is recommended that you do
Dear Milan sir,
I should be running the update statistics on the remote server(sqlcdscluster\sqlcds), correct?
'auto create statistics' and 'auto update statistics' options are turned on.
I should be running the update statistics on the remote server(sqlcdscluster\sqlcds), correct?
Yes. Overall, it is better to work with this query on the server where it actually runs.
I will have to say that I'm a little puzzled, though. The remote query appears to have no filer condition, why the number of rows returned is the number of rows in the table, and even if statistics are out of date, the cardinality should be decently accurate.
I am not sure that you'r architect's idea of replacing four-part tables with OPENQUERY is a good move. Bring over the full remote table to filter locally may be expensive.
As I have said before, you should try to remote from linked servers altogether. Have your forwarded my sentiment to your architect?
Hi Chaitanya,
I completely agree with what Erland said. Please discuss with your architect and if possible ask the reason for taking that approach.
Dear Erland,
Architect is saying NOT to replace linked server with OPENQUERY. Is this what you are also saying?
Architect is saying NOT to replace linked server with OPENQUERY. Is this what you are also saying?
Sorry, I missed the "not" there.
For what I have been saying, I like to need to make some clarification. OPENQUERY is one way to use a linked server. Using tables in four-part notation is another. And to muddle the waters further, there is OPENROWSET, which does not use the definition of a linked server, but still runs a distributed query.
What I have been saying is that you should consider to move the databases that now are on different servers to be on the same server, so that you don't need any distributed queries of any sort. Distributed queries are generally problematic for performance. Optimizing is more challenging, and there is network overhead for moving the data. The latter becomes even more amplified if you would move one of the databases to the could, and keep the other on-site.
Going back to the question that started this thread, I am a little puzzled by the incorrect estimate, because for a one-table query without a WHERE condition, I would expect a correct estimate - or, if there is a permissions problem, an estimate of 1. Since I missed the "not", I assumed that this was OPENQUERY, but I guess that the table is accessed with four-part notation?