MRP Trouble

Over the weekend the evil MRP crashing after an hour of trying to “delete unfirm jobs” returned with no solution in sight. So I spoke with my cousin who knows a thing or two about SQL databases as well, and he reminded me of the awesome power of being able to look at the currently running queries using a sproc like sp_WhoIsActive2 or my-favorite-query.

So I kicked off MRP Full Regen, let it get to the Deleting Unfirm Jobs step, waited a few minutes, and then ran sp_WhoIsActive2 - and there was the culprit, a query that appears to be clearing Transfer Order suggestions and had been running for over 5 minutes already:

delete a 
from Erp.TFOrdDtl a inner join Erp.PartDtl c on a.Company = c.Company and a.TFLineNum = c.TFLineNum 
inner join Erp.JobHead b on c.Company = b.Company and c.JobNum = b.JobNum where b.Company = 'LEER' 
        and b.JobClosed = 0 and b.JobFirm = 0

Now I’m having deja vu. I turn this into a SELECT * statement instead of a delete and run it, but I have already guessed how many results there are going to be. Sure enough: zero. But even the select query takes several minutes to run. So I have SQL Query Analyzer show me the execution plan, and there is a table scan and a proposed missing index. I’m in kind of a hurry to get MRP working, so I took the proposed index as is - it might potentially be optimized more or have fewer included columns?

Missing Index Details from SQLQuery3.sql
The Query Processor estimates that implementing the following index could improve the query cost by 61.1133%.

USE [Epicor]
ON [Erp].[PartDtl] ([Company],[JobNum])
INCLUDE ([Type],[PartNum],[DueDate],[RequirementFlag],[Quantity],[AssemblySeq],[JobSeq],[OrderNum],[OrderLine],[OrderRelNum],[PONum],[POLine],[PORelNum],[PartDescription],[IUM],[SourceFile],[CustNum],[StockTrans],[FirmRelease],[RevisionNum],[TargetOrderNum],[TargetOrderLine],[TargetOrderRelNum],[TargetWarehouseCode],[TargetJobNum],[TargetAssemblySeq],[TargetMtlSeq],[Plant],[InvLinkNum],[PlantTranNum],[TFOrdLine],[TFOrdNum],[TFLineNum],[SourceDBRecid],[NonPart],[BOMChanged],[BaseQty],[BaseQtyUOM],[InvtyQty],[InvtyQtyUOM],[JobFirm],[PartDtlSeq],[SysRevID],[SysRowID],[ContractID])

Now I can run the select statement and it takes only 10-20 seconds (and still returns zero results). The delete statement takes 2 to 3 minutes, but this is a big improvement over running for an hour and then timing out, killing MRP!