In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views.
Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really fun time once when I implemented an indexed view, and they called me back the next day screaming because their app was down. My bad. (sigh)
To use these features, NUMERIC_ROUNDABORT has to be set OFF, and ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER must all be set to ON.
People just kinda look at each other and go, “Uh, how am I supposed to know what connection options people have?”
That’s where sys.dm_exec_sessions comes in. For all current sessions, it shows everything except NUMERIC_ROUNDABORT. You can run a query like this every minute via an Agent job, log it to table, and then go back to see if any culprits were found:
|
1 2 3 4 5 6 7 8 9 |
SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND (ansi_nulls = 0 OR ansi_padding = 0 OR ansi_warnings = 0 OR arithabort = 0 OR concat_null_yields_null = 0 OR quoted_identifier = 0) |
Ta-dah:
For bonus points, you can filter on database_id, but just be aware that someone can be in a system database like MSDB and still do fully qualified DUI operations against a user database.
11 Comments. Leave new
Yes I believe I had the same issue with an indexed view as I implemented it and then informatica failed immediately. I would have thought it was a pretty transparent operation but nope! Had to immediately get rid of it and of course no one would approve changing the settings so it would work. Think it may have used JDBC.
Something I’ve noticed with Informatica that’s caused me issues is that it defaults to DWmsss25.so, the legacy SQL Server driver. Using DWsqls25.so tends to help Informatica get along better with newer SQL Server features, and it’s also significantly faster in my experience.
What I meant by ‘no one would approve changing the settings’ was that they were unwilling to make any such configuration changes to their ‘working configuration’.
But they were willing to move the database(s) (PeopleSoft) having the performance issues to ORACLE.
Rewrote the query slightly make it easier to scan what settings are “off”.
SELECT
[OddSettings] = fnOddSettings.[settings], s.*
FROM sys.dm_exec_sessions s
CROSS APPLY (
SELECT [ansi_nulls] = CASE WHEN ansi_nulls = 0 THEN ‘ansi_nulls=0;’ ELSE ” END,
[ansi_padding] = CASE WHEN ansi_padding = 0 THEN ‘ansi_padding=0;’ ELSE ” END,
[ansi_warnings] = CASE WHEN ansi_warnings = 0 THEN ‘ansi_warnings=0;’ ELSE ” END,
[arithabort] = CASE WHEN arithabort = 0 THEN ‘arithabort=0;’ ELSE ” END,
[concat_null_yields_null] = CASE WHEN concat_null_yields_null = 0 THEN ‘concat_null_yields_null=0;’ ELSE ” END,
[quoted_identifier] = CASE WHEN quoted_identifier = 0 THEN ‘quoted_identifier=0;’ ELSE ” END
) fn1
CROSS APPLY (
SELECT [settings] = fn1.[ansi_nulls] + fn1.[ansi_padding] + fn1.[ansi_warnings]
+ fn1.[arithabort] + fn1.[concat_null_yields_null] + fn1.[quoted_identifier]
) fnOddSettings
WHERE is_user_process = 1 AND
(s.ansi_nulls = 0
OR s.ansi_padding = 0
OR s.ansi_warnings = 0
OR s.arithabort = 0
OR s.concat_null_yields_null = 0
OR s.quoted_identifier = 0)
ORDER BY fnOddSettings.[settings],
[s].[session_id]
I tried:
EXEC sys.sp_configure N’user options’, N’64’
GO
RECONFIGURE WITH OVERRIDE
GO
and it had no effect. New sessions still show as having arithabort = 0. I’m not having any issues from it, just trying to get ahead of potential problems. We use filtered indexes, but not on computed columns (yet).
Allegedly, ARITHABORT is implied as ON if ANSI_WARNINGS is ON, and ansi_warnings = 1 for all my connections to this server. Yet arithabort = 0 for many connections.
Hi Mark,
I am pretty sure all you did was change the server/database default. These are all connections options and various providers (e.g., ODBC, ADO, SQLNCLI) set up different default values when a connection is established. That’s one reason why you sometimes see different behavior/results when you run a query in SSMS and then run the same from an application.
Hi Mark,
You are right. This is why I’ve changed my query like this:
/* only check ArithAbort when compatibility level is below 90: see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql */
OR (arithabort = 0 AND COALESCE(D.compatibility_level,0) <90)
Henrik, all of my databases have the compatibility level set to 100 or higher. Yet I see sessions with arithabort = 0 even though ansi_warnings = 1. I wonder if they must therefore be specifically setting arithabort = 0 and overriding the implied setting from ansi_warnings.
One source that is in this state consistently are four connections from SQLAgent: Alert Engine, Job invocation engine, Generic Refresher, and Email Logger, all of which show as using ODBC connections.
If I change the instance default and restart the Agent service, those four sessions come back with arithabort = 1, so they must be accepting the default. It seems logical to assume that other new sessions on that instance that have arithabort = 0 must be setting it explicitly.
hi Mark, Yes my databases show arithabort=0 and have Compat level > 90 too. But I think the check on ArithAbort can be ignored, when Compat Level >=90 as per the link to BOL.
(@p1 int,@p2 int)SET ARITHABORT ON; select superadmin from bas_l_person where personid= @p1 and addresspoolid= @p2
sys.dm_exec_sessions–>arithabort= 0
for the above query, result is arithabort=0