December 9, 2013 at 8:27 am
We have a third party BO running the query around 3AM in the morning. Query uses several views to pull the data. When BO runs it at 3AM, it errors out "A time out occurred while waiting to optimize the query. Rerun the query." This is what I find in event log of the server. But when I run the query through SSMS, it runs within 40 secs. And same results when BO runs it some later time in the morning. I do not find any missing index or anything like that and not sure where to begin troubleshoot from.
Here is the stats from the pro-filer:
Reads: 190032
Writes : 0
CPU: 86977
Duration: 39581
This was working fine for several years without issue and starting to have the problem now all of the sudden.
Any help on this would be greatly appreciated.
December 9, 2013 at 8:41 am
This shows that your system is experiencing memory pressure at that time. Have you confirmed that nothing new was installed on your box that could be causing external memory pressure on the SQL Server? Have you started any new processes that could be running at the same time? Has an old process started taking longer and is now running at the same time. Do you have enough memory for your system? You can following the troubleshooting steps in this article to see if you are having memory issues (search for your error message):
December 10, 2013 at 6:58 pm
I agree with Keith.
Some other process may be running at about the same time or the earlier process is now taking longer time to overlap with this. Try looking at the scheduled tasks and SQL jobs.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply