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 KizerSQL Server MVP since 2007http://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 KizerSQL Server MVP since 2007http://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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
find query that causes severity 016