3

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?

3
  • 4
    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. Aug 13, 2012 at 13:12
  • 1
    You like troubleshooting weird performance issues? sommarskog.se/query-plan-mysteries.html
    – gbn
    Aug 14, 2012 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, 2012 at 8:52

1 Answer 1

7

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.

1
  • Eight years and four version later, nothing has changed. I really wouldn't hold my breath on Microsoft ever deprecating anything. Jul 16, 2020 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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