0

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.
4
  • 1
    The max memory setting will be different. You can use option (querytraceon 2335) check this answer (dba.stackexchange.com/a/55254/8783). – Kin Shah Apr 29 '16 at 13:23
  • 1
    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? – Tom V Apr 29 '16 at 13:30
  • When is the last time that satistics have been updated and indexes rebuilt in these environments? – Arthur D Apr 29 '16 at 13:43
  • 3
    Also see this and this. Different hardware, different data size, and different @@version can all definitely contribute to getting different plans. – Aaron Bertrand Apr 29 '16 at 14:06
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:

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.

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.