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:
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)
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.