Query performance blows out when full query run

Michael Pine 2014-02-03 22:20:59

Have this reasonable query and the individual join query's by themselves all run fine, the slowest one on our test environment is about 13 seconds, however when this total query is run, basically run time is endless, just keeps on going.

/storage/attachments/758-LargeWaterUsers_Anonymized.pesession
Aaron Bertrand 2014-02-04 00:46:22
"Run time is endless" typically means there is blocking going on. Did you check to see if this query is blocked (even if it doesn't seem logical)?
mbourgon 2014-02-03 23:15:02
Any way to get the actual plan? Just right off the top of my head, the "run time is endless" usually signifies the stats are out of date or the tables sizes are drastically different.

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/

Aaron Bertrand 2014-02-04 00:46:55
We'll probably have to settle for estimated plan, since if the query runs forever, we'll also be waiting forever for the actual plan. 🙂
Michael Pine 2014-02-04 02:54:05
Ok, attached is the Actual Execution Plan! So this take about 3-4 minutes to run, I did find a problem where someone else had been working on the table in Test and accidently removed our Unique and Clustered Index, so I put those back and got things down a bit 🙂
link text

/storage/attachments/759-largewaterusers_aep.sqlplan
mbourgon 2014-02-04 21:32:12
Michael, one thing that pops up immediately is the whole estimated-vs-actual count. There's one case where instead of 119 rows it's 40 million, and the data size is 1(gb?) instead of 3k. Sorting the "top operators" page by est cost, there's a couple others where the est/actual are waaay out of whack. The cost estimation on the plan is negligible, but IIRC it only counts CPU, not IO, so the fact that you're having to do nested loops on such a large set could prove to be a problem.

(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

Kevin 2014-02-14 21:46:08
If you open the execution plan in Plan Explorer and look at the Top Operations tab (at the bottom of the screen), you can see a lot of details that Aaron and Michael are describing. A few things jump out at me:

  • 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.
February 3, 2014
No Comments