SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A time out occurred while waiting to optimize the query. Rerun the query.


A time out occurred while waiting to optimize the query. Rerun the query.

Author
Message
SQL_Surfer
SQL_Surfer
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1658 Visits: 1098
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.
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2320 Visits: 979
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
Amit Raut
Amit Raut
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1233 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search