Same query same data different server different query plan

Question

I have a query that hits a view, which references other views etc...several levels down. The query plan is a bit complex.

I run the query on 2 different servers. On both servers, the number of rows in referenced is roughly the same and the query returns 3 rows in both environments..

In production, sql server chooses a query plan that looks to create a resultset of one of the underlying views without applying the join clause first. this results in 8x more page reads.

In QA environment, sql server applies the join clause first and minimizes the rows returned by that subtree. the subtree in prod returns 42k actual rows and in QA 2 actual rows.

What would cause sql server to choose a less optimal plan?

QA is sql 2012 sp3
Prod is sql 2012 sp1
no physical reads in either case.
prod stats io shows 42k logical reads on worktable. I am assuming this is the resultset from that subtree. 
QA does not show this.

• Kin Shah: The max memory setting will be different. You can use option (querytraceon 2335) check this answer (dba.stackexchange.com/a/55254/8783).
• Tom V: We need a lot more information about the server's configuration, what's the output of SELECT * FROM sys.configurations and DBCC TRACESTATUS(-1) for example?
• Arthur D: When is the last time that satistics have been updated and indexes rebuilt in these environments?
• Aaron Bertrand: Also see this and this. Different hardware, different data size, and different @@version can all definitely contribute to getting different plans.

Answer 1

Since you are running SP3 in QA and SP1 in production (assuming you are running with Trace Flag 4199) you are missing out on a lot of performance related fixes in your production environment.

My first suggestion would be to test the query and compare the generated plan on servers of the same build number.

Without details of the query and the underlying tables (which may be complex to post if you're talking about views referencing views, joining views) it's hard to tell which issue you are running into exactly, but for example these have been fixed since SP1:

• FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012 or SQL Server 2014
• FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2 or in SQL Server 2012
• FIX: Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014

But the list goes on and on.

Other than that it could be any configuration difference (MaxDOP, memory in the server, activated trace flags) or it could be statistics that are different.

In any case it's hard to compare between builds that are so far apart (SP1 and SP3)

I would suggest running the same build in QA as prod anyway because you want your QA-users to be testing the exact situation as they will be running in prod.