Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

A time out occurred while waiting to optimize the query. Rerun the query. Expand / Collapse
Author
Message
Posted Monday, December 9, 2013 8:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 16, 2014 12:56 PM
Points: 332, Visits: 868
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.


Post #1521124
Posted Monday, December 9, 2013 8:40 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 8:56 AM
Points: 592, Visits: 925
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):
http://technet.microsoft.com/en-us/library/cc966540.aspx




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521138
Posted Tuesday, December 10, 2013 6:58 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
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.
Post #1521722
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse