0

Recently, one of my colleague working in SQL development got into a problem like this: a procedure ran fine on all environments, but production, which has the most resources. Typical case of parameter sniffing, but the profiler indicated that only one query in the whole procedure took very much to execute:

UPDATE  a
SET     status_id = 6
FROM    usr.tpt_udef_article_grouping_buffer a
        LEFT JOIN (SELECT DISTINCT buying_domain_id, suppl_no FROM usr.buyingdomain_supplier_article) b ON  a.buying_domain_id = b.buying_domain_id
                                                                                                        AND a.suppl_no = b.suppl_no
WHERE   a.tpt_file_id = @tpt_file_id
        AND a.status_id IS NULL
        AND b.suppl_no IS NULL

As I am biased towards development (I have little administration experience), I suggested that this query should be rewritten:

  • replace LEFT JOIN (SELECT DISTINCT ...) with NOT EXISTS (SELECT 1 ...)

  • put the appropriate index on table usr.tpt_udef_article_grouping_buffer(SSMS suggested an effort reduced by 95% when query was run outside the procedure)

Also, multiple queries from the procedure shared the same pattern.

I know that parameter sniffing is more related to the plan constructing when running the procedure for the first time after its (re)creation and I think it is also favored by high cyclomatic complexity.

My question is:

Does the way queries in the procedure are written (bad execution plans from the beginning) favor parameter sniffing appearance or just worsen their effects?

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164
  • Apart from your question: both your suggestions would indeed improve your execution plan. – TT. Jan 02 '16 at 10:11
  • Parameter sniffing optimizes an execution plan for a certain parameter the first time it is run. If it is then run with a different parameter, the cardinalities may be way off and your execution plan might not be optimal (or far from optimal). Rearranging your query might improve an execution plan, which is then further optimized by parameter sniffing (for a certain parameter). The two are orthogonal AFAICT. You may have optimized your query further by rewriting but after parameter sniffing is applied, you may have a suboptimal plan for other parameters. – TT. Jan 02 '16 at 10:23
  • Ok, so no correlation between execution plan and parameter sniffing. I am trying to convince back-end developers to pay attention to how they write their queries and the execution plans they generate. In this case, my suggestion was not put into practice, as using local variable greatly reduced procedure execution. Thanks for pointing out the orthogonality. – Alexei - check Codidact Jan 02 '16 at 10:45

1 Answers1

0

Your only parameter here is a.tpt_file_id = @tpt_file_id and if this is parameter sniffing, then the cases must be such that for certain tpt_file_id there are thousands (or more) records, and for certain there is few (or none).

The other reason you get different plans in production than test environment is that the machines are different. You usually have a lot more memory and more CPUs / cores in production environment, causing optimizer to choose different plan and of course if your row counts in the tables are not the same, it of course can lead to into a totally different plan.

You can check this with using option (recompile) to see if the plan changes or look at plan cache that what was the value of the parameter used to create the plan. It can be seen in the properties of the leftmost object in the plan.

Changing the select distinct into exists clause is probably a good idea, and of course indexing the tables properly.

James Z
  • 12,209
  • 10
  • 24
  • 44
  • Yes, that parameter was sniffing and the quick solution adopted was to declare a local variable and assign the parameter to it. This solved the problem and the queries remained untouched (although bad execution plan is quite clear and is reproducible on every environment). I was wondering about a possible connection between bad plan and parameter sniffing, because this is the first project where it happens quite often and it is also the first project where back-end developers are not concerned about execution plans. – Alexei - check Codidact Jan 02 '16 at 10:41
  • 1
    Instead of local variable you can also use `optimize for` hint, either with unknown which is the same as using a local variable, or if you know a good value you can also give that. – James Z Jan 02 '16 at 11:10