Different plan for a query In APP and SQL

Question

Different plan for a query In APP and SQL

mah_j

SSCrazy

Points: 2931
More actions
June 18, 2013 at 4:26 am

#276467

Hi
I execute a query from web App and I get the select statement from sql profiler.
I run exactly the statement without any changes directly in sql right away at that time,but I get 2 different plan in sql and in sql profiler.SQL and App are behaving diffrent.
Developer team uses linq in App and the statement in sql profiler is in sp_executesql format.
Why I get 2 different plan for a query in App and SQL?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Are you getting different plan for this query only and every time? Have you tried other query to check plan in app and sql?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

mayurkb Ten Centuries Points: 1155 More actions · Answer 2

It depends on the options that are selected and the sniffed parameters of the query. SQL Server caches the same query in the plan cache for different options.

Generally, the distinction is ARITHABORT. ARITHABORT setting is OFF by default when the query is run from the .net, ODBC, OLEDB app. And while running it from SSMS it is ON.

mah_j SSCrazy Points: 2931 More actions · Answer 3

This problem is for most of the query that is because of parameter sniffing,so some times in app it uses the correct plan as the same in SSMS,and some times it uses the bad plan because of the first parameter that creates the plan and is saved in cache.

My problem is that when in app the query has a bad plan exactly at that time with the same parameter in sql profiler,in SSMS it has the correct plan.Whay the plan is diffrent when it uses the plan for that query from cache?

I exactly run the query from sql profiler without any changes even space or capital letters.

And my query is without any option.

I dont understand what does ARITHABORT do?And what is the relation between plan and ARITHABORT?

mayurkb Ten Centuries Points: 1155 More actions · Answer 4

ARITHABORT doesn't really do anything. It is just a setting, you can set it off from the tools->Options. Once you do that you will see the same plan that you saw while profiling the app. This doesn't really solve the problem, but it does get you closer. Look at the attached screenshot for setting it off.

Parameter sniffing depends on the kind of data you have in those tables. The problem arises when the data is not evenly distributed. There are multiple ways you can solve the problem. But I think as a developer you should not do anything about it. Because the distribution of the data can be different for different consumers of your application. So leave it to the DBA.

Post your queries and execution plans and we can see if we can find some other solution like adding index etc.

DiverKas SSCrazy Points: 2049 More actions · Answer 5

mayurkb (6/19/2013)
ARITHABORT doesn't really do anything. It is just a setting, you can set it off from the tools->Options. Once you do that you will see the same plan that you saw while profiling the app. This doesn't really solve the problem, but it does get you closer. Look at the attached screenshot for setting it off.
Parameter sniffing depends on the kind of data you have in those tables. The problem arises when the data is not evenly distributed. There are multiple ways you can solve the problem. But I think as a developer you should not do anything about it. Because the distribution of the data can be different for different consumers of your application. So leave it to the DBA.
Post your queries and execution plans and we can see if we can find some other solution like adding index etc.

The issue with that advice is, the developer CANT do anything about it, since LINQ/ODBC/OLEDB or whatever data access method wont support OPTION(OPTIMIZE FOR, which is how you would want to reduce the Parameter Sniffing issue, if that is indeed whats going on.

In reality, if LINQ/ODBC/OLEDB is being used to create and run an ad hoc query (and that is exactly what it is) that causes Sniffing to come into play, the only recourse is to make it a proc and force LINQ/ODBC/OLEDB to call that instead. DBA and the Devs will have to work hand in hand for that solution.

I am not necessarily saying ALL queries have to be a stored proc, only those that cause this issue to surface.

mayurkb Ten Centuries Points: 1155 More actions · Answer 6

"OPTION optimize for" if not the only way to resolve sniffing issues. There are multiple ways to resolve it.

The thing that I am trying to say is first we need to determine if it is sniffing issues at all and can we resolve it any any other means. That is why I asked to post the queries and plans.