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?