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]
GO
CREATE NONCLUSTERED INDEX LEER_IX_PartDtl_JobNum
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])
GO
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!