0

I have a strange problem with some specific sql statement performance. I have an sql statement (contents, I think is not important). When running through .NET application (using EF as a DB access; SQL gets parameterized) and catching data with SQL Profiler I get following results:

  • CPU: 28.253
  • Reads: 8.503.799
  • Writes: 0
  • Duration: 54.274 (a little more than 54 seconds)

However, when running the same SQL statement through SSMS (first executing set statistics io on and set statistics time on) I get following results:

Table 'mytable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 16 ms, elapsed time = 138 ms.

Both SQLs are executed under same user context, but obviously different connection. As you can see, running the same query through SSMS is way faster than running it through application.

Where should I look for differences, e.g. what triggers different reads and duration? Can it be, that the problem is parameter sniffing (I don't see how, although)?

Improve this question
1

2 Answers 2

Reset to default
1

The problem is because the execution plans generated in the two executions, application / SSMS, are different.

As you assumed, the problem is parameters sniffing.

Why is that, even if the query you're running is the same?

Well, because apart from looking for a execution plan in the plan cache for a executed query, SQL Server also looks at session settings (ANSI_NULLS, ARITHABORT etc.). The default session settings in these two scenarios are different.

I believe .NET EF parameters have ARITHABORT set to OFF whereas in SSMS it's ON (have a look at this article - Slow in the application, fast in SSMS).

So, in the end you will have in your plan cache 2 different execution plans for the same query, but for different session parameters.

I would start to look for re-writing this query. I've recently had the same problem and I've tried matching session parameters in both sessions (application / SSMS) and for a while the execution plan that was generated was fine.

But some time after, when the plan got pushed out of the plan cache a new sub-optimal plan was generated and continued to be used (parameter sniffing again), so this is quite a vicious cycle and I don't recommend it as a long-term solution.

As a long-term solution I would recommend re-writing your query / queries and maybe add some proper indexes if you don't already have them in place.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thank you for the answer. What about adding "option (recompile)" at the end? That would basicly solve parameter sniffing error, but it will force recompile every time. I've checked and all indexes are placed ok, and overall it's not that complicated query, it's just that table has several millions of rows.
@Jure You could solve it with OPTION (RECOMPILE), but do you really want to do that? High number of recompiles / sec will translate in higher CPU usage. I'd use Kimberly Tripp's slogan here : "recompile when unstable, cache when stable" - and when talking about stability I'm talking about data skew. Is your data so very skewed that you'd require a new plan for each of your parameters? If yes then choose "RECOMPILE", else prefer to cache it.
@Jure What I'd do is try to split the query into two or more queries. 1st query is where I bring in all the data from the big tables and put all those rows in a #temp table. SELECT a,b,c into #tmp from bigTblA .. then in my second query I'd join this #tmp table with my other smaller tables (if you have any of those). You should see a better and constant performance over time.
I already do select only from one table, so there are no joins. I think actually, that data distribution is so un-even, that it comes to performance problems with some data combinations.
@Jure If your data is very skewed then you could go for recompiles, just be aware that that will increase the CPU usage, based on how often the query is called.
|
0

This query will help you to get multiple execution plan. SELECT * FROM sys.dm_exec_procedure_stats WHERE object_id = ?

Check the SSMS SET options: Tools > Options > Query Execution > SQL Server > Advanced

You can see the difference between both of the executions with below query: SELECT * FROM sys.dm_exec_plan_attributes (plan_handle)

Option----->Value

  • ANSI_PADDING----->1

  • Parallel Plan----->2

  • FORCEPLAN----->4

  • CONCAT_NULL_YIELDS_NULL----->8

  • ANSI_WARNINGS----->16

  • ANSI_NULLS----->32

  • QUOTED_IDENTIFIER----->64

  • ANSI_NULL_DFLT_ON----->128

  • ANSI_NULL_DFLT_OFF----->256
  • NoBrowseTable Indicates that the plan does not use a work table to implement a FOR BROWSE operation.----->512
  • TriggerOneRow Indicates that the plan contains single row optimization for AFTER trigger delta tables.----->1024
  • ResyncQuery Indicates that the query was submitted by internal system stored procedures.----->2048
  • ARITH_ABORT----->4096
  • NUMERIC_ROUNDABORT----->8192
  • DATEFIRST----->16384
  • DATEFORMAT----->32768
  • LanguageID----->65536
  • UPON Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled.----->131072
  • ROWCOUNT - Applies To: SQL Server 2012 to SQL Server 2016----->262144

How to count this values and options?

  • From the result table we would get the first value. Here it is 4345.

  • Next from the table we need to the find the highest value next to 4345. In this case it is 4096.

  • Once we have these two values we need to find the difference of them. That is difference of 4345 and 4096, which would result in 249.

  • We need to follow the above steps till end. The difference in values are options which are used for execution plan of that particular Procedure.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.