Are we using SQL Server Management Studio (SSMS) all wrong? Do you ever have a case where an application developer comes up to you and say that the query inside the application is performing slowly but when you run the same query in SQL Server Management Studio, it's blazingly fast? Here's why: SET ARITHABORT ...
Evidence -- fired up SQL Profiler and ran a query in Entity Framework 4. Pay attention to the arithabort setting.
-- network protocol: TCP/IP set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed
Running the same query in SSMS:
-- network protocol: TCP/IP set quoted_identifier on set arithabort on set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed
As one of my boys would say, "What the .. ?"
Did some research and came up with a treasure trove of information, but it's not in our or our DBA's subconscious so I felt it should be shared and discussed some more.
“You should always set ARITHABORT to ON in your logon sessions. Setting ARITHABORT to OFF can negatively impact query optimization leading to performance issues.” https://msdn.microsoft.com/en-us/library/ms190306.aspx
Wait, there's a box that says Caution and in it says: 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.
Talk about a conflict! This needs to be cleared up. (contacted the MSSQL team via twitter)
Looking elsewhere to see if we can get this cleared up pronto. Found a great source of information from Erland Sommarskog a SQL Server MVP:
“...by far most common reason for slow in the application, fast in SSMS in SQL 2005 and later is parameter sniffing and the different defaults for ARITHABORT.” http://www.sommarskog.se/query-plan-mysteries.html
Continuing on --
“You have also understood that you can verify that this is the case by running this command in your query window:
SET ARITHABORT OFF
and with great likelihood, you will now get the slow behaviour of the application also in SSMS. If this happens, you know that you have a performance problem related to parameter sniffing. What you may not know yet is how to address this performance problem, and in the following chapters I will discussion possible solutions, before I return to the theme of compilation, this time for ad-hoc queries, a.k.a. dynamic SQL.”
Back to Entity Framework's generated and verbose SQL which is used in client applications (along with arithabort off). The verbose aspsect of the generated SQL is to prevent or minimize parameter sniffing that causes performance issue when you run the query. Sommarskog calls it forced parameterization.
Per Sommarskog, "Forced parameterisation can be a big performance saver for an application that does not used parameterised statements, but there is little reason to use it with well-written applications."
So I ask all DBAs (and developers!) who uses SSMS to experiment this by going into Tools > Options > Query Execution > SQL Server > Advanced and uncheck SET ARITHABORT.
I'm sure DBAs would have to go back and forth SETting the ARITHABORT ON or OFF when they're troubleshooting application queries vs. doing system queries.