September 17, 2026 at 1:14 pm
When I run my query from DW, which uses a linked server, it times out. I then take that query, run directly on a linked server with select top 1 and it takes 85 minutes.
Now I restore the linked server DB in test. I run the same query from test DW and it takes 3 seconds. SO the data is same, query is the same so I am guessing prod stats are out of date? What else could be the reason for it?
Just trying to learn the art of troubleshooting and where to even start from.
Any help is highly appreciated
"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]
September 18, 2026 at 1:39 pm
Without more information on the setup of the two prod servers (DW and remote / linked server,) anything I suggest is based on an educated(ish) guess, so keep that in mind.
But to me, this "feels" like it could be the network between the DW and linked server. I'm presuming that your test servers (and presuming you have two test servers, one for the DW and one for the linked server database) are on the same physical network, and presuming that your production servers (DW and linked) are not (as an example, the DW server is somewhere on the US east coast and the linked is somewhere on the US west coast.)
There could also be issues with the query itself to the linked server.
I'm disinclined to think it's statistics or indexing, partly because when you restore a database backup, UNLESS you do DBCC update statistics, the statistics in your test environment are the same as what's in the production environment. Not saying this might not be part of the problem, just that it doesn't feel likely (yet)
September 18, 2026 at 3:12 pm
depending on how server is configured, driver version and other stuff it could be worst that that.
a query on a linked server can explode if the filtering does not happen on the remote server but on the local server - in this case ALL the rows of the remote table are copied locally and then the filtering applies.
you should be able to identify if this is the case by using sp_whoisactive (or similar) on the prod server when executing the query to see what is the exact query being executed (vs what you think its doing)
September 18, 2026 at 4:57 pm
Users use MS Access which connects to a specific DSN, DSN connects to DW, which fetches the data from ServerA. His process takes about 15, 20 minutes to run or at least that was the last execution time last week. When I ran the code in DW yesterday, it took 42 minutes, but when I used openquery, the query took 3 seconds. When I run a query which uses sys.dm_exec_query_stats along with other tables and I filter it by all the tables which are used in the query, I see 0 result.
So no stats on the table? The Access user does not want to use openquery because of the condition they use in access I guess so now I have to figure out a way to run it DW fast.
I am updating stats today on ServerA.
Anything else I can do to optimize the process? Any other info I can provide?
"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]
September 18, 2026 at 5:18 pm
Honestly based on your reply, frederico_fonseca has it right, the query is trying to pull ALL the remote table(s) through the link before doing any filtering. I'm basing this on you saying that using OPENQUERY (which executes on the REMOTE server) is comparatively blazing fast.
If the end-user refuses / can not change anything in their MS Access query, then the next option *I* can think of to speed things up would be to create a local table(s) that get populated from the linked server early in the morning and the user change their query to pull from the local table. If they also require "live" data from the remote end, well, then you have to convince them to make changes because there's really not much else you're going to be able to do.
Hmm. Maybe, and I'm not familiar enough with what can be done in Access, rewrite their query into a stored procedure that's on the DW, that gets called from Access, and uses OPENQUERY to hit the linked server. IF this is doable, it might be a solution.
September 28, 2026 at 6:04 pm
The end user was able to run his query from MS Access in 7 seconds. Nothing was done on the DB side. I am not sure how.
"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]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply