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.