sql server consulting logo
sql server consulting is our only business

Free SQL Server Consultation

Sometimes you only need the answer to a question or two. If 15 minutes of our time can help you solve a SQL Server problem, we are happy to help. No charge, no strings attached.

Call 877.201.3428 toll-free

Using SQL Server in the Enterprise - Archives

Sign up for your FREE subscription to this SQL Newsletter

Give this article a Google +1!

Problems with ANSI Settings

Issue 6 Number 9 September 2016


Back in March my monthly newsletter was about parameter sniffing, exploring why a specific query might execute quickly in one case and very slowly in another case.

This month I would like to drill down on one aspect of that problem, the role that ANSI settings play if a query runs quickly when executed from management studio and slowly when executed from your application (or vice-versa). Often this is caused by a difference in the ANSI settings between the two connections.

These settings can be the default settings for Management Studio or different settings established by your application in its connection code.

A difference in these settings sometime leads to a big difference in execution plans and that in turn leads to differences in execution times.

Differing ANSI settings is certainly not the only thing that causes these performance differences but it is frequently the cause. There is no simple fix. It is a matter of experimentation. But knowing that the ANSI might be a factor in your problem and knowing what the settings are currently should get you pointed in the right direction.

How Do We Find the Current ANSI Settings?

ANSI settings in SQL Server are stored as bitwise integers that can be either enabled or disabled. The function @@OPTIONS sums the integers for the enabled bits and returns a single number that can be compared with the settings of other connections. Using the code below you can go one step further and check which specific settings are enabled and which are not.

  • DECLARE @options INT
  • SELECT @options = @@OPTIONS
  • PRINT @options
  • IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
  • IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
  • IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
  • IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
  • IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
  • IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
  • IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
  • IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
  • IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
  • IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
  • IF (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
  • IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
  • IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
  • IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
  • IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

If ANSI Settings are My Problem, How do I Fix Them?

You might first look at our newsletter on parameter sniffing and try those remedies first. Those problems are similar to these, so you might find a simpler fix.

Changing the ANSI settings is relatively simple. You can do that with the SET function. Knowing what settings to change is difficult. No single factor decides what the settings should be. I have refrained from suggesting what they should be because different circumstances call for different settings. However this link will take you to a discussion of what settings are appropriate in specific circumstances. I don't know how accurate they are because there are many factors involved, but it is a place to start.

Afterward by the Author

I hope this information has been helpful to you. I would appreciate any feedback you can give me about this article or recommend future articles you would like to see. If you enjoyed reading this article, you might like to receive this monthly newsletter via Twitter. If so, follow me.

As always, if you have questions or comments give me a call toll-free at 1.877.201.3428 or send me email.

Kurt Survance, SQL Consulting, Inc.

Return to Top

Go to SQL Consulting Home

On this page:

Performance issues with ANSI Settings

Elsewhere on this site:

Call SQL Consulting
mcse microsoft certified systems engineer
mcdba microsoft certified database administrator