Many times I face questions from development teams “Why ado.net queries run so slow and my SSMS queries are so fast?”.
The most common reason of this behavior is about the option ARITHABORT.
Ado.net defaults are different than SSMS settings:
Your application connects with ARITHABORT OFF, but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses, but SQL Server will compile the procedure anew, sniffing your current parameter values, and you may get a different plan than from the application. So there you have a likely answer to the initial question of this article. There are a few more possibilities that we will look into in the next chapter, but the 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.
This and many more issues can be fixed if you read “Slow in the Application, Fast in SSMS? Understanding Performance Mysteries” by Erland Sommarskog.
You can check the ARITHABORT option, running the query below:
DECLARE @ARITHABORT VARCHAR(3) = 'OFF'; IF ( (64 & @@OPTIONS) = 64 ) SET @ARITHABORT = 'ON'; SELECT @ARITHABORT AS ARITHABORT;