Why ado.net queries run so slow and my SSMS queries are so fast?

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.

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;

Good reading!

Advertisements

Danilo Braga is a Database Administrator with over nine years of experience. He is a Microsoft Certified IT Professional (MCITP) Database Administrator and Developer. His experience includes solutions for management, assessment and implementation of SQL Server environments making use of best practices, performance tuning, and high availability.

Tagged with: ,
Posted in Issues, Performance tuning
4 comments on “Why ado.net queries run so slow and my SSMS queries are so fast?
  1. Guilherme Vassão says:

    Hi Danilo!

    I am a developer and i’ve been using Entity Framework (ORM by Microsoft) in my applications so far. I can’t notice any performance issues YET. BUT, you know, Entity Framework translate my queries to SQL. Am i loosing performance? If yes, how come Entity Framework is slowly than SPs?

    Like

  2. Jorris says:

    Ehm, this part confuses me: “…but when you run the query in SSMS, ARITHABORT is ON and thus you will not reuse the cache entry that the application uses..”

    It seems like you are saying there is no reuse with ARITHABORT set to ON

    MS also has some info on this topic at: https://msdn.microsoft.com/en-us/library/ms190306.aspx

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: