Same schema, same data, same execution plan?

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

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

StackExchange Database Administrators Q#151752, answer score: 8