Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

In our application we have the following tiers for data processing:

  • .NET orchestration application that works out which SSIS packages to fire
  • SSIS packages that do much of the work and call Stored Procedures
  • Stored procedures to perform calculations and read/write data where SSIS data flows are not appropriate

One stored procedure runs in 40 seconds if executed from SSMS, and approximately the same time if the SSIS package is called manually.

If however, the SSIS package is called by the .NET app, then the query takes more than 14 hours to run, and flat lines all processors.

Looking at the execution plans, it seems that if the procedure is run from SSMS or the SSIS package is fired manually and then calls the procedure, SQL Server uses a sensible plan based on current statistics.

However, if the SSIS package is called from the .NET app, then SQL server create a new plan, using totally the wrong statistics. The following things made no difference:

  • Updating stats
  • Clearing the cache
  • Restarting the instance
  • Setting WITH RECOMPILE on the procedure execution

We fudged resolution by using a MAXDOP hint in the query, but my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

share|improve this question
2  
It sounds like parameter sniffing and your assumption that the plan was generated with the same parameters is not correct. If you still have the bad plan look and see what the parameter compiled values and run time values are in the XML. See Slow in the Application, Fast in SSMS? for more. – Martin Smith Oct 25 '12 at 7:06
When you say "The following things made difference" you mean they helped and you got a good plan afterwards? That was what I was assuming in the comment above. – Martin Smith Oct 25 '12 at 7:11
@MartinSmith - sorry, typo. Was meant to read "made no difference" – Pete Carter Oct 25 '12 at 20:13

1 Answer

up vote 4 down vote accepted

It looks like you have mostly answered your other questions in your monologue, and the things that made a difference are the standard list of things to do to force a recompile after being afflicted by parameter sniffing.

To answer your last question, the one marked my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

The answer is that the plan cache is very specific about SET settings that are in effect on the session that is presenting the query. A different SET setting could need a very different plan, such as the setting SET ANSI_NULLS. Very likely, your connection from SSMS has different settings from what your .Net application. This is easily verifiable by using SQL Server Profiler to trace for Existing Connections and Login Audits, which show you the SET settings.

The effect is that the seemingly same query will result in a Cache Miss and a new one has to be created, which will be somewhat based on the parameters being presented to SQL Server for this instance of the query.

share|improve this answer
@richrdthekiwi - thanks, the team are investigating this, but I don't understand why the SET options would be different? same machine, .NET guys tell me nothing being passed explicitly in the connection string? – Pete Carter Oct 25 '12 at 20:17
1  
@PeteCarter - SSMS has a different default for ARITHABORT than your application probably does. – Martin Smith Oct 25 '12 at 20:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.