3

I have a T-SQL stored procedure in SQL Server 2014 SP2 that takes 3x as long on the server as it does from my local environment.

I restored my local database to what was on the server, so schema and data are the same.

I'm a programmer, not really a DBA, so bear with me on this.

The execution plans seem the same until you look at the XML. On my local environment, a Stream Aggregate is used for one of the queries to make a distinct select, while on the server, it looks like a Sort is used with a hash.

It appears that the fact that the server has less processors than my (beast) local machine is contributing to the problem. Every query takes a little longer on the server. Could this be the reason that the sort/hash is chosen on the server instead of the Stream Aggregate?

Is there a way to force the Stream Aggregate?

Here is the SQL for the stored proc: http://pastebin.com/kkXM3Bsf

Here is the execution plan for my local environment: http://pastebin.com/0u7SHxnM

Here is the execution plan for the server: http://pastebin.com/9sE9DfM0

8

The hardware configuration can affect plan selection as I discuss in my answer to the related Q & A:

difference in execution plans on UAT and PROD server

The question doesn't quantify the "3x" performance difference, but assuming it is important enough to be concerned about:

  • You can force the broad shape of a plan completely using a Plan Guide or USE PLAN hint. This will prevent the optimizer choosing a different plan shape over time as the data volume and distribution changes, so it is important to review forced plans regularly.
  • You can often get closer to the desired plan shape with targeted FORCESEEK hints on tables that should be accessed by a seek (e.g. driven by a nested loops join) rather than a scan (with a hash or merge join). In your case, the FORCESEEK hint(s) could be applied to Account and/or CustomerLocation. This may be preferable to forcing the whole plan shape as in the previous point, as it gives the optimizer freedom in other areas of the plan.
  • You could give the optimizer a better chance with the @country is null or ca.Country = @country predicate by using a RECOMPILE hint, or writing two separate queries (one for the NULL case; the other for non-NULL) surrounded by an IF @country IS NULL test. Try the two separate queries first.
  • You should be able to avoid the sort spill with a QUERYTRACEON (7470) hint. This is documented in:

    FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct

  • There is no supported way to force a Stream Aggregate (over a Sort Distinct), without forcing the whole plan.

  • You should consider indexing the heap table variable (a PK at least), and/or using a temporary table instead.

I would start by writing the query as two alternatives (or using the RECOMPILE hint, if you can tolerate a recompilation on each execution), with the 7470 trace flag enabled, and go from there. Plan forcing would be my least favourite option.

If the server has a very large amount of memory, and plans routinely prefer memory-consuming operators when they should not, you could consider testing your whole workload with TF 2335 as in the linked Q & A. I would try the more targeted approaches first.

See also my article: Parameter Sniffing, Embedding, and the RECOMPILE Options

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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