Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been given a case where a customer is experiencing this error - once in a while:

Msg 8624, Level 16, State 21, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

They're using our software, and running it on SQL Server 2008 R2 (RTM) and at compatibilty level 100 (SQL Server 2008). The database however was originally created on a 2000 or 2005 machine (can't reproduce anymore) and then moved to 2008 R2 recently.

The stored procedure in question has an ugly looking INSERT statement which grabs data from ten (yes!) "copies" of a given table, all joined with RIGHT OUTER JOIN against one another (same table - ten JOINs against it).

The one solution I found that seems most appropriate has to do with various settings - it's recommended to use:

set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set ARITHABORT ON

set NUMERIC_ROUNDABORT OFF 

OK - sure - I can set these before every stored procedure I create (or alter).

My question to the database gurus out there would be: any risk when I set these as default settings for my database?

E.g.

ALTER DATABASE MyDB SET ANSI_NULLS ON

and be done with it? Thoughts? Insights? Recommendations?

share|improve this question
3  
No, not at all. In fact, the ability to turn ANSI_NULLS off has a limited life. Soon enough that won't even be an option, it'll always be on. The only time I've ever seen any form of reasoning for setting ANSI_NULLS off, is because there's developers that can't fathom the true meaning of NULL, and therefore want to use typical conditional logic with it. Again, terrible reason and I look forward to the permanent on for ANSI_NULLS. –  Thomas Stringer Aug 13 '12 at 13:12
1  
You like troubleshooting weird performance issues? sommarskog.se/query-plan-mysteries.html –  gbn Aug 14 '12 at 7:49
2  
@gbn: well, not really - the query processor cannot come up with a plan - nothing to performance-troubleshoot there! :-) –  marc_s Aug 14 '12 at 8:52
add comment

1 Answer 1

up vote 5 down vote accepted

It is not a risk at all. In fact, the ability to turn ANSI_NULLS OFF has a limited lifespan. Soon enough that won't even be an option, it'll always be ON. The only time I've ever seen any form of reasoning for setting ANSI_NULLS OFF, is because there are developers that can't fathom the true meaning of NULL, and therefore want to use typical conditional logic with it. Again, terrible reasoning and not justifiable. I look forward to the permanent ON setting for ANSI_NULLS.

share|improve this answer
    
Thanks for your insights, Shark! –  marc_s Aug 15 '12 at 4:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.