Troubleshooting extremely long running query (SQL Server 2012)

Question

We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours). A week ago, this same query completed in less than a minute. I've restored the DB from last week to confirm the end user's story, and sure enough, even with similar row counts with last weeks and yesterday's backup, the difference in query run time is there.

Table1 contains 1.4 million rows. Table2 contains 16k rows. In this case, none of the records from Table2 exist in Table1, so when the query is actually allowed to complete, no records are returned.

The table schemas are identical, with the exception that Table1 has a non-unique, non-clustered index on Field10. There are no other indexes that exist on either table. All fields in the join are (NVARCHAR(40), null) types.

The query looks like this:

DELETE Table1 
FROM Table1 t1 
INNER JOIN Table2Temp t2
 ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2 
 AND t1.Field3 = t2.Field3 AND t1.Field4 = t2.Field4
 AND t1.Field5 = t2.Field5 AND t1.Field6 = t2.Field6 
 AND t1.Field7 = t2.Field7 AND t1.Field8 = t2.Field8
 AND t1.Field9 = t2.Field9 AND t1.Field10 = t2.Field10 
 AND t1.Field11 = t2.Field11 

If I simply do a "SELECT * INTO Table3 FROM Table1" and "SELECT * INTO Table4 FROM Table2" and repeat the query about, substituting Table3 for Table1, and Table4 for Table2, the same query (against the new tables) takes 4 seconds.

I'm not an expert at Execution plans, but it looks okay to me. No suggested indexes, index seek on Table1 (cost of 89%), and a table scan on Table2 (but cost is only 9%). Here's a link to the actual execution plan that has been anonymised. http://pastebin.com/jcyATezX

There is no blocking that is taking place. When using sp_WhoIsActive, I can see that the Reads are sky-rocketing into the billions.

Again, this is a boxed application, so I can't really change the query. Any ideas on what I'm missing here? It's driving me nuts that I can't figure this out.

Answer 1

After some excellent help from Kin, wBob, and Aaron Bertrand, I finally found my root cause: Table1 statistics needed updating.

I had already confirmed that statistics were updated 2 days prior during our weekly index maintenance, so didn't really think anything of it. However, I gave it a try, and that made the query run time go from 2hrs 30min down to 2 seconds!

Command run was:

UPDATE STATISTICS Table1;

Thanks for all the help, particularly in the article "The "Runaway" Query"