Does query form have an influence on getting into parameter sniffing?

Question

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?

Answer 1

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.