December 8, 2025 at 5:28 am
Hi
I have an overnight process that moves allot of claims records Been working fine for many years.
Now it hits this part of the code (across a linked server) and gets the (1596567ms)MEMORY_ALLOCATION_EXT wait type and will sit there forever and never move any records - no delta movements at all. Used to complete in an hour now will sit there for over 12 before I have to stop it to let other steps in the job complete.
I am at a loss as what to do next. Both servers seem to have plenty of memory 512 GB.
December 8, 2025 at 12:07 pm
This wait usually isn’t due to a real lack of RAM, but rather SQL Server waiting for a large memory grant because of a plan change, stale statistics, or data growth. Since this job worked fine for years, the most likely cause is a bad execution plan or incorrect row estimates, especially with the linked server involved. I’d check the execution plan while it’s stuck, update statistics on the main tables on both servers, and see if any other large queries are consuming most of the query memory at the same time. If possible, try breaking the load into smaller batches or running the logic locally on the remote server, as distributed queries can easily run into these memory waits.
December 14, 2025 at 5:49 am
I've found that if you are using the 4 part naming in a linked server, performance can be unpredictable and a "quick fix" is to change the query to use OPENQUERY. If you are joining onto the table or using it in a subquery, I recommend putting the data from the linked server into a table variable or temp table to work on it and you might notice a performance improvement as well.
We had a query that most of the time completed like you in an hour or so, but every now and then it would run for 12+ hours and cause all sorts of problems. We changed the logic to pull the data from the linked server into a table variable and it fixed the performance issue for us.
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.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply