0

I have faced a sudden performance-drop issue in a VB6 (ADO/ODBC to SQL Server) application i'm maintaining which seems to be solved by adding SET ARITHABORT ON before some SELECT queries.

The questions are: Would it be wise to create a generic procedure to set ARITHABORT ON in every select query of he application? What are the dangers in doing that? Can this setting cause the SELECT queries to return invalid data (especially if they contain aggregate functions) without the user/developer noticing it?

UPDATE: Please have in mind, this application run on SQL Server 2014 servers with Merge Replication enabled.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FaultyOverflow
  • 153
  • 1
  • 9

1 Answers1

0

This suggests you currently have ARITHABORT OFF. This is a very bad idea, Microsoft even added a specific warning on BOL:

"You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues."

"Warning The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting."

So, yes, I would set ARITHABORT to ON, but from the start. If the rest of the environment is build to expect ARITHABORT OFF, I would be very hesitant to change such a fundamental behavioural setting.

As for the performance issue, you shouldn't even want to fix it with the ARITHABORT setting. I would look into the data why the performance drop occurs in the first place. Something must have changed, if you find out what, you can correct it in a less fundamental way.

HoneyBadger
  • 14,750
  • 3
  • 34
  • 48
  • Yes, the default in applications is OFF, which didn't seem to have a serious impact in the application, until recently. While the link you provided has important info, it doesn't answer my question. Is it possible such a change will cause unintended results in the application (especially since replication is involved)? – FaultyOverflow Jun 23 '17 at 20:18
  • I doubt replication is a factor in this issue. It depends on how the application is build. What validity checks are in place in the software, what responses are expected. I would expect some application errors. I would not so much expect faulty results, but it is possible. – HoneyBadger Jun 23 '17 at 21:57