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.
| 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/ |
 |
|
|
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? |
|
|
|
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/ |
|
|
|
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.
|
|
|
|
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/ |
|
|
|
|
|
|
find query that causes severity 016