ARITHABORT should be set ON in all modern development work. Many new features require that setting, for example XML methods, indexed views (as you mentioned), and indexed computed columns. Some queries will fail if connection settings are incorrect, or you may see reduced performance for example if an index on a computed column cannot be used, and the computed column values must be re-evaluated at runtime.

The database and server default settings for things like ARITHABORT only come into play if the client does not specify a setting. They default to OFF only for backward-compatibility reasons. Almost all client libraries set things like ARITHABORT when they connect. See http://msdn.microsoft.com/en-us/library/ms175088.aspx for full details.

You say: "all .NET applications rely on the default database setting of OFF". Can you expand on what you mean by this, because that runs counter to my experience.

There are any number of ways to obtain query plans - both before and after execution. Using sys.dm_exec_cached_plans will show the before-execution cached plan. You can use sys.dm_exec_procedure_stats to obtain a plan_handle for that view. You might also use Profiler.