Huge difference between estimated rows and actual rows

Question

Huge difference between estimated rows and actual rows

coolchaitu

SSChampion

Points: 10334
More actions
August 21, 2015 at 4:47 am

#303648

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.

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

coolchaitu (8/21/2015)
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.

The tablesources in the plan are all on sqlcdscluster\sqlcds. The whole query would be more efficient if it were set up as a stored procedure or view on server sqlcdscluster\sqlcds, alternatively you could use openquery to send the entire query to the remote.

So you have two different SQL Server instances talking to each other. Has your architect advised you on how they should talk to each other in Amazon Cloud? Is this what you are asking?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

coolchaitu SSChampion Points: 10334 More actions · Answer 2

Dear Chris sir,

Openquery can tune/improve performance but the architect told not to replace linked server with openquery. This is because we are migrating to cloud and architect said that it will be easy to migrate if they are kept as linked servers. Please let me know if there are any other places or areas where this proc can be tuned.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

coolchaitu (8/21/2015)
Dear Chris sir,
Openquery can tune/improve performance but the architect told not to replace linked server with openquery. This is because we are migrating to cloud and architect said that it will be easy to migrate if they are kept as linked servers. Please let me know if there are any other places or areas where this proc can be tuned.

Your architect needs to do a little reading. From BOL:

"OPENQUERY (Transact-SQL)

Other Versions

Applies To: SQL Server 2014, SQL Server 2016 Preview

Executes the specified pass-through query on the specified linked server."

If you wish to keep the calling syntax close to what you currently have then use OPENQUERY(). Otherwise, create a view or a stored procedure on the remote server and call that. Either way, you end up executing the whole query on the remote server. Right now you are using the worst possible option which you can see from the performance.

I'm not a "sir", I'm just a TSQL coder.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

coolchaitu SSChampion Points: 10334 More actions · Answer 4

Sir is a respectful term of address. I am an entry level while you all are already in the dba field for years. Hence, as a token of courtesy, I am addressing as sir.

Y.B. SSChampion Points: 11649 More actions · Answer 5

coolchaitu (8/21/2015)
Sir is a respectful term of address. I am an entry level while you all are already in the dba field for years. Hence, as a token of courtesy, I am addressing as sir.

Oh snap...LOL

I have to admit I don't like being called sir either but he does make a good point. 😉

SELECT quote FROM brain WHERE original = 1
0 rows returned