So I've determined that the erratic behavior of my SQL Server is because of .Net SqlClient Data Provider's default setting of
SET ARITHABORT OFF. With that said, I've read various articles that debate the best way to implement this. For me, I just want an easy way because SQL Server is suffering and my query tuning hasn't fully transcended across the app (and obviously adding the
SET in a sp DOES NOT WORK).
In Erland Sommarskog's brilliant article about the topic, he basically suggests taking the safe approach by altering the app to issue
SET ARITHABORT ON for the connection. However, in this answer from a dba.stackexchange question, Solomon Rutzky offers both an instance-wide and database-wide approach.
What ramifications am I missing here with setting this instance-wide? As I see it ... since SSMS has this set
ON by default, I see no harm in setting this
ON server-wide for all connections. At the end of the day, I just need this SQL Server to perform above all else.