Query performance blows out when full query run
You could (http://blog.sqlauthority.com/2011/01/05/sql-server-copy-statistics-from-one-server-to-another-server/) try scripting out the stats from Prod, stick them in QA, and see what happens. (remember to drop the existing stats in QA, though)
Another article on the same topic:
- The Actual Rows are way different from Estimated Rows. Update your statistics.
- Your derived tables are causing massive data reads (clustered index scans) from the lowest level tables, such as DIM_Customer.You might consider using pulling only the needed data from those tables up into temporary tables and then do your final necessary joins from there.