Huge difference between estimated rows and actual rows

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.


August 21st, 2015 6:59am

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

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 7:49am

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.

August 21st, 2015 8:18am

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?

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 8:29am

Hi Chaitanya,

I completely agree with what Erland said. Please discuss with your architect and if possible ask the reason for taking that approach. 

August 21st, 2015 8:59am

Dear Erland,

Architect is saying NOT to replace linked server with OPENQUERY. Is this what you are also saying?

Free Windows Admin Tool Kit Click here and download it now
August 21st, 2015 12:10pm

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?

August 21st, 2015 5:33pm

We have Update Stats Job running once in a week. Should we change it to run twice in a week?
Free Windows Admin Tool Kit Click here and download it now
September 2nd, 2015 2:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics