Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 find query that causes severity 016

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-01-26 : 11:23:35
Hi,

I am trying to find an offending query in my 2008 R2 production.

Alert system caught this Severity 016 alert with this message:

DESCRIPTION: The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Anyone knows a script that can nail the query down?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-26 : 12:31:16
Do you have sp_WhoIsActive running regularly to capture active queries? We run it every 30-60 seconds and use it frequently to go back in time to determine what happened.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-01-27 : 10:49:19
Thank you for your idea.

The who-is-Active seems point to Database Mail process start/shut down as the culprit. The 016 alert seems associate with some of the database mail restart cycle.

I am chasing down that rabbit hole.

Anyone has any thought?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-27 : 12:00:27
If it's pointing at Database Mail being the culprit, I would verify what build you are running. Is it SP3? If not, I would start there. If you are on SP3, I would open a case with Microsoft on this as it isn't something we can resolve.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2015-01-27 : 12:19:36
The server is on 10.50.4000, which is sp2.

wow, need sp3 to fix this.

There are these configure system parameters in database mail configuration wizard. Some said adjusting them may help.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-27 : 12:22:36
You could try adjusting the settings. Not sure if you will have any luck. To me it sounds like a bug which could be addressed in the service pack. I haven't looked at the bug list to know for sure though.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -