How to speed up this query?

Areeb 2014-08-03 21:37:00

When table1 has 110 million rows, this took about 24 hours to finish! Can someone please help speeding this up? Thanks.

Aaron Bertrand 2014-08-03 21:50:51
If you want help with the query itself, you'll need to post the actual query. Currently, using the anonymizing function within Plan Explorer removes the query text.
SQLkiwi 2014-08-04 16:23:23
The uploaded plan shows the query ran for 67.232 seconds, performed relatively little I/O, and used hardly any CPU. From the information given, it seems most likely the 24 hour execution was simply blocked by another transaction.
Kevin 2014-08-04 17:55:19
As Paul (SQLKiwi) mentioned, there's not actually a lot of work going on here. There is a decent amount of time spent on PAGEIOLATCH_EX, that is buffer cache IO operations of an exclusionary nature. So it looks like the query is spending a lot of time waiting to get update locks on rows/pages that are already locked by other processes. (I also noticed a TOP operator. Why would you want a TOP operation on an Update?)

There are also a few index statistics that are out of date.

Aside from those clues, I can't tell you much without the query text.

Hope that helps,