August 5, 2025 at 9:15 am
Scenario 1 (Direct Connection - Fast):
On Server B, when using SQL Server Management Studio (SSMS) to connect directly to Server A by specifying its IP address and using a valid SQL Server login and password, executing the view vwA in database DB01 completes in approximately 1 second.
Scenario 2 (Linked Server - Slow):
A linked server named LS01 has been created on Server B, pointing to Server A. However, when the query SELECT * FROM LS01.DB01.dbo.vwA is executed directly on Server B (targeting the linked server), the same operation takes approximately 50 seconds to complete.
Environment:
Both Server A and Server B are running SQL Server 2008 R2 standard edition
even if use select to query 100000 pieces of records from one table thru linked server it taking about 20 seconds ;but it taking about 1 second thru direct connection to query the same statement. my question is how to improve the performance to query thru linked server? thanks
August 5, 2025 at 9:31 am
You have discovered why most of us dislike linked servers !
How did you declare and configure the linked server?
e.g. did you enable "collation compatible"?
Keep in mind data transfer between your server and the declared linked server may not be that fast and will consume tempdb space.
It also depends on the actual query that is being executed.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 5, 2025 at 1:45 pm
so as Johan mentioned, linked servers leverage tempdb. so if you are adding a WHERE or an ORDER BY, or joining it to your local data, the whole table goes into tempdb, and then any WHERE statements, joins or sorts are applied.
instead of this:
SELECT * FROM LS01.DB01.dbo.vwA;
can you run this to see if it performs better?:
EXECUTE ('SELECT * FROM DB01.dbo.vwA') AT LS01;
Lowell
August 5, 2025 at 2:17 pm
The biggest issue is where does the processing occur. If you're running a query, with a WHERE clause, across a linked server, most of the time, ALL data will be transferred across the network, then filtering occurs locally. To get filtering to occur on the remote server, you have to push the execution over there. You can try sp_executesql and pass the parameters through. That will result in the filtering being done remotely, and you'll only move the data you actually want. OPENQUERY can do it as well, but it's not one I've used as much.
But yeah, total agreement with Johan. Don't do this.
Yes, I get it, linked servers are convenient. And yes, linked servers are way easier than building out a full, proper, data movement and data access application. And yeah, it's built in to the product so it must be OK to use, right? Well, no. There are actually a bunch of things you CAN do within SQL Server that you really shouldn't. Nesting views. Nesting functions. Using multi-statement user defined functions at all. Nolock. And LinkedServers. All things in the product, but that you shouldn't do.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 5, 2025 at 2:54 pm
so as Johan mentioned, linked servers leverage tempdb. so if you are adding a WHERE or an ORDER BY, or joining it to your local data, the whole table goes into tempdb, and then any WHERE statements, joins or sorts are applied.
instead of this:
SELECT * FROM LS01.DB01.dbo.vwA;
can you run this to see if it performs better?:
EXECUTE ('SELECT * FROM DB01.dbo.vwA') AT LS01;
thanks for you kind help , I executed "EXECUTE ('SELECT * FROM DB01.dbo.vwA') AT LS01;" it also ran slowly
August 5, 2025 at 3:03 pm
You have discovered why most of us dislike linked servers !
How did you declare and configure the linked server? e.g. did you enable "collation compatible"?
Keep in mind data transfer between your server and the declared linked server may not be that fast and will consume tempdb space. It also depends on the actual query that is being executed.
if don't use linked server, how can I achieve my request ? could you kindly give me some suggestion? thanks!
August 5, 2025 at 3:06 pm
The biggest issue is where does the processing occur. If you're running a query, with a WHERE clause, across a linked server, most of the time, ALL data will be transferred across the network, then filtering occurs locally. To get filtering to occur on the remote server, you have to push the execution over there. You can try sp_executesql and pass the parameters through. That will result in the filtering being done remotely, and you'll only move the data you actually want. OPENQUERY can do it as well, but it's not one I've used as much.
But yeah, total agreement with Johan. Don't do this.
Yes, I get it, linked servers are convenient. And yes, linked servers are way easier than building out a full, proper, data movement and data access application. And yeah, it's built in to the product so it must be OK to use, right? Well, no. There are actually a bunch of things you CAN do within SQL Server that you really shouldn't. Nesting views. Nesting functions. Using multi-statement user defined functions at all. Nolock. And LinkedServers. All things in the product, but that you shouldn't do.
As I don't know how to use sp_executesql to achieve my request, could you please instruct me how to do it? many thanks!
August 5, 2025 at 4:28 pm
if don't use linked server, how can I achieve my request ? could you kindly give me some suggestion? thanks!
One way to handle removing linked servers is to handle it at the application layer. Have the application request the data from each database and combine the data as needed.
Alternately, if you don't need real-time data, ETL process - pull the required data across on a schedule to keep all of the data local. Doesn't work as well if you need real-time data though.
If ETL won't work, service broker can provide eventual data consistency between two systems. Can be a pain to set up correctly (lots of ways to mess things up such as having 1 conversation per message or leaving a conversation open too long or poorly written triggers) but once it is up and running it is basically set and forget.
My opinion - application layer changes, while likely the most complex solution, is also the best solution as it provides better scalability. ETL would be my second choice as it doesn't take long to set up and Service Broker would be my last choice due to the pain to set up and support if problems arise. Service broker is a pretty cool tool and is fun to work with but can be a nightmare when things go wrong or if you set it up wrong to begin with.
As for your question about sp_executesql, SQL Shack has a good demo on it here - https://www.sqlshack.com/introduction-to-sp_executesql-stored-procedure-with-examples/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 6, 2025 at 12:49 am
892717952 wrote:if don't use linked server, how can I achieve my request ? could you kindly give me some suggestion? thanks!
One way to handle removing linked servers is to handle it at the application layer. Have the application request the data from each database and combine the data as needed.
Alternately, if you don't need real-time data, ETL process - pull the required data across on a schedule to keep all of the data local. Doesn't work as well if you need real-time data though.
If ETL won't work, service broker can provide eventual data consistency between two systems. Can be a pain to set up correctly (lots of ways to mess things up such as having 1 conversation per message or leaving a conversation open too long or poorly written triggers) but once it is up and running it is basically set and forget.
My opinion - application layer changes, while likely the most complex solution, is also the best solution as it provides better scalability. ETL would be my second choice as it doesn't take long to set up and Service Broker would be my last choice due to the pain to set up and support if problems arise. Service broker is a pretty cool tool and is fun to work with but can be a nightmare when things go wrong or if you set it up wrong to begin with.
As for your question about sp_executesql, SQL Shack has a good demo on it here - https://www.sqlshack.com/introduction-to-sp_executesql-stored-procedure-with-examples/
Appreciate your kind and great help! I know the common usage of sp_executesql, but I don't know if we can use sp_executesql on linked server, thank you!
August 6, 2025 at 1:22 pm
The example from SQLShack is good. Here's the Microsoft documentation on it. You 100% can use it with your linked servers. You just have to code it. Follow the examples. Experiment and learn. It's the only way to improve. However, I have a caveat that I detail on it here. Just something to keep in mind. There are examples at all three places, SQLShack, Microsoft and my blog.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 9, 2025 at 9:25 pm
However, when the query SELECT * FROM LS01.DB01.dbo.vwA is executed directly on Server B (targeting the linked server), the same operation takes approximately 50 seconds to complete.
We had similar issues. Although it worked very well with a table and a list of column names from the table, I suspect the following will work for your view, as well.
SELECT *
FROM OPENQUERY([LS01],'SELECT * FROM DB01.dbo.vwA') AS RemoteQuery
;
Note that, just like in other areas of SQL, "RemoteQuery" in this code is an alias that can be changed to whatever you like and that alias can be used in the "SELECT List" to identify where individual columns are coming from. Individual column names can be used in the SELECT in the OPENQUERY and outside the query, as well.
I have NOT tried joining to these things. I've only used them in a nightly transfer (from an IBM system/DB2 database, actually)
I'm also assuming from the simplicity of the example that I provided, that I don't need to explain what is between the parentheses of the OPENQUERY.
A lot of people don't know this but you can also insert and update the remote data using OPENQUERY. The MS documentation on that provides some examples. Here's the link, which can easily be found by searching for "OPENQUERY T-SQL" without the quotes.
https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql
Note that if your remote table contains XML that you want to bring over, that requires the trick of explicitly naming columns in the OPENQUERY with a conversion to NVARCHAR(MAX) and then reconverting that to XML in the outer query.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 11, 2025 at 2:52 pm
Just to add to the advice - I would advise against SELECT * unless it is absolutely necessary. The reason being if the source metadata changes (column added or column removed), you may get unexpected results. Plus, when pulling data from anywhere, it is best practice to only pull the data you require.
Now that being said, it MAY make sense for a SELECT * vs a targeted SELECT, but I would check if you really need a * or if you should be using named columns.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 12, 2025 at 10:17 pm
Just to add to the advice - I would advise against SELECT * unless it is absolutely necessary. The reason being if the source metadata changes (column added or column removed), you may get unexpected results. Plus, when pulling data from anywhere, it is best practice to only pull the data you require.
Now that being said, it MAY make sense for a SELECT * vs a targeted SELECT, but I would check if you really need a * or if you should be using named columns.
Yep... agreed... The reason for the * here is because he's selecting from a VIEW and should be returning only what is needed in the view to begin with. There could actually be the advantage of not having to change this code if they decide to add columns to the view.
As with all else, "It Depends".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply