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.