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.
- SET NOCOUNT ON
- 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.