sql server – Why is the query in ETL via SSIS slow but via a local stored procedure it is fast?

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 (somehow a gorilla got involved) and it ends with a ? instead of a parameter. Here is a sample of the query:

FROM     acme 
widget ON acme.acme_id = widget.acme_id
(acme.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.