October 29, 2024 at 6:58 pm
I have a view on ServerA that fetches some data from ServerB and ServerC. The linked servers are configured properly. The query itself runs fine on ServerB and ServerC. But when I run it from ServerA, I get 'Query timeout error', yet some days, it runs fine. Some days it executes in a minute but today, it ran for 31 minutes.
I just need a recommendation on what to look for and where to look for this type of timeout-related issue. I hardly see any activity on ServerA since it is a pretty beefy reporting server. No one else has issues but 1 user.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
October 29, 2024 at 9:46 pm
Welcome to the wonderful world of linked server performance troubleshooting,
Some will say never use linked servers and look for alternatives. https://www.brentozar.com/archive/2021/07/why-are-linked-server-queries-so-bad/
Others will say they are OK in certain circumstances. I fall in this camp. Your issue could easily be network latency but there can be other issues. Can you post your query? There are methods for improving linked server performance but it's a black art and very different from troubleshooting performance issues on a single server.
Some techniques I have used are:
October 30, 2024 at 11:51 am
I lean towards, let's try, as much as possible, to simply avoid linked servers. They're very attractive appearing. Looks like a simple, clean, quick, and most of all EASY, way to get data between servers. And then reality hits. Repeatedly. And hard. Reality hits so hard. No punches are pulled.
Anyhoo, yeah, people are still going to do it. See, attractive. See, easy. And then it stinks. I agree with @Chrissy321, openquery can, not will, but can, fix a lot of issues. It pushes the processing down to the linked servers, so if you can filter the data over there, before you try to move it across the network (and since you're probably doing all query processing locally, it has to do a full scan remotely, and return every bit of data in order for you to then filter it locally, yeah, no doubt it times out sometimes). Pushing the filtering down to the remote server and only moving the data you need to move makes a huge difference. It's not a panacea for all performance issues with linked servers, but openquery covers up a lot of sins.
Query tuning mechanisms still apply here. Get the plans for the query on the remote server so you can ensure that, indeed, the query you have is running well, using indexes & statistics appropriately, and filtering data on the remote machine. Combine that with standard query tuning practices on the local server as well.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply