I'm seeing the classic 'runs fast in studio manager but slow in application' problem. Sounds like it might be parameter sniffing. However, my experience with ETL and SSIS is zero.

From the DBA I received the following query and it ends with a ? instead of a parameter. Here is an obfuscated sample of the query:

FROM     sqltable..tablex 
sqltable..tableWork ON tablex.work_id = tableWork.work_id
(tablex.update_ts >= ?)
  • According to the DBA, the question mark is replaced with a 'time/date' argument that is one hour in the past.
  • When I execute this same query locally from a stored procedure, passing in a parameter that is one hour in the past, it returns in less than a second. (which to me, means it 'can' use the existing index)
  • Watching this execute from the ETL, it takes minutes and the execution plan shows table scans.
  • There is an update_ts index.

The query engine recommends a second update_ts index with several include columns. I'd like to avoid that if possible as it will add memory pressure and I'm not convinced it solves the real issue. Thoughts?

This seems like a case of the query statistics being skewed and when the query engine sniffs the parameter it avoids using the existing index because the estimated number of rows is beyond the threshold.

My questions:

  1. How does the ? in the SSIS query get handled by sql server? I know parameter sniffing is a complex issue. I've been studying this: http://www.sommarskog.se/query-plan-mysteries.html
  2. If it is the query engine sniffing the parameter (of one hour in the past) and thinking the estimated number of rows is beyond the tripping point, what do I do to fix this? The DBA has refused the hint OPTIMIZE for RECOMPILE as an option and I can't say I disagree. (He has a point regarding the bug history) However, these queries happen ONLY from the ETL on schedule times and perhaps that is reason enough to use the HINT regardless of the potential bug??

Also, this is a long issue I've been struggling with. All of these posts are related to this same issue. What a voyage of discovery:

Is this an excessively large 'Lock' time and is it indicative of a problem?

SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?

Any advice is greatly appreciated.

This should be the actual execution plan from the local stored procedure version. This version returns within 1 second and exhibits the behavior I WISH the ETL would have:


Now, this is a screen grab of the ETL version that takes minutes to complete. Sorry I cannot provide more details on this particular query: A bad ETL estimated execution plan

This is a screen grab from a profiler trace done over an hour period. I think this is how the ETL commands are being executed. I don't yet, know why these all have the same time. I also need to find the prepare as well. Look at those cpu, reads, and duration columns!

Profiler trace of ETL query

  • 2
    Upload the query plan to Paste The Plan and share the link in your question. Not sure why the OPTION RECOMPILE would be concern for an ETL query.
    – Dan Guzman
    Commented Mar 31, 2021 at 15:28
  • 2
    Where is the query used within SSIS? I presume a Data Flow as an OLE DB Source (OLE uses a ? as an ordinal based parameter placeholder but ODBC also uses the question mark). How are you measuring that the source is the laggard as opposed to any transformations and the destination?
    – billinkc
    Commented Mar 31, 2021 at 15:47
  • @billinkc the ? with query was provided by the DBA in an email. When I look at the execution plan on the production database, it does have a @ P1 instead of a ?. I had forgotten that fact. As for your last sentence - you'll have to help me. All I can do is look from the production database, not the ETL and I don't understand 'transformations' and 'destination' in this context.
    – D-K
    Commented Mar 31, 2021 at 16:09
  • 1
    To answer my question, you'll need access to the SSIS package. The source query might run blazing fast in both SSMS and the package. It might be that something after the data is extracted from the database, the rest of the operations they are performing are slow. Really pushing my luck on assumptions, they are loading new and changed data from the source db to the destination. They might be using the "OLE DB Command" object for the updates and that fires off singleton queries to update the target table. It's a common pattern and is fine until data volume don't support one-off changes.
    – billinkc
    Commented Mar 31, 2021 at 16:25
  • 1
    You need to capture the sp_prepare in your trace and check the datatype of your parameters, if you want to use the index you're much more likely to succeed if the parameter datatype matches the column datatype Commented Apr 6, 2021 at 8:32

2 Answers 2


We had a similar issue with a query that was called from EntityFramework. Was fast in SSMS, but slow from application.

It turned out there was an error in mapping of parameters, their type, which caused the query from application to do a scan, due to the query becomming non-SARGable.

After fixing this issue, the query was fast from application.

  • Thank you. This has been a pet theory of mine and hearing your account helps. I have a profiler trace and I'm attempting to learn enough about the data that's saved in the trace to see if this theory pans out. So far I'm still behind the learning curve.
    – D-K
    Commented Apr 6, 2021 at 14:07
  • 1
    Sorry for the delay and miss on bounty. Life intervened and I wasn't available to handle this. As for the query and issue - slowly being resolved. We've learned that the ETL is indeed the issue. We've also solidified that running a local stored procedure with the same query and passed parameter executes in less than a second. So - something about how SSIS is executing these same queries remotely - is the issue. I won't have the keys to that castle for a another period of weeks. Customers can be slow sometimes. Thanks again for your answer.
    – D-K
    Commented Apr 21, 2021 at 13:41

I wanted to share some of the findings and success stories related to this long running saga.

It is amazing what SSIS and ETL can do if you spend the time to learn it.

It turns out that OPTION (RECOMPILE) has brute forced the issue and queries that used to take 4 minutes, now take 800 ms.

Also poorly thought out hacks to get data from the source DB outside of the SSIS eco system... Gone!

It pays to have the keys to the castle and spend time learning the system.

Just to give an example of the fun... one of the systems took 18 total minutes (the full ETL) to do a 30 minute window extraction.

Now we can do a 15 minute window and it takes 90 seconds for everything. Extraction Transform and Load!

So the moral of the story? Spend time learning SSIS. Spend time tuning the system.

Your Answer

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

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