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:
http://www.mssqltips.com/sqlservertip/1999/how-to-create-a-sql-server-clone-statistics-only-database/
link text
(not to be a sqlsentry shill, but in the plan explorer, go to the diagram, right-click and change cost from CPU to IO/CPU or IO; numbers will change. Also change Line Widths from rows to MB – it shows you're pulling 1gb off of fct_billing_trans, and that's going into your loop.)
Couple other things:
Key Lookup on FCT_Billing_Trans – see if there's another way to pull/filter. It's almost nothing in the plan, but that's by far the most amount of data you're moving around.
Check the Clustered Index Scans. On some of those tables, like DIM_Customer, you might be able to do a covering index, and that's an easy 14% of the overall query.
Hope this helps!
mbourgon
- 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.