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

4 comments

  1. 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

    1. Hi Guilherme!
      The bad thing for using Entity Framework is that, there will be one more step to interpret and transform the query, and then the data will be returned. Besides, you also cannot control the execution plan of the query. So, comparing it to “classic” ADO.NET queries/sp, you are paying a price for the benefits you gain.
      You can find more datails here, https://msdn.microsoft.com/en-us/library/orm-9780596520281-01-16.aspx
      I hope I’ve helped!

      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