I am experiencing a problem with our ERP system that is causing my company a huge headache and I am not sure if there is anything that I can do to try to resolve.
Essentially, when someone at our firm approves an expense report from an employee it causes the entire Expense Report Application to hang. The following is the query:
SET TRANSACTION isolation level READ uncommitted;
SELECT statuscode,
completion,
completionstatuscode,
reviewaction,
reject,
description,
duration,
cfgapprovaltype.autoapprove,
cfgapprovaltype.concurrentapprovals,
cfgapprovaltype.autoapprovedetaillines,
cfgapprovaltype.approvallevel
FROM cfgapprovaltype
LEFT OUTER JOIN approvalworkflow
ON approvalworkflow.itemtype_uid =
'63DF2CF7B1224152BDA9EA37ECF16F4D'
AND approvalworkflow.step = '3'
AND active = 'Y'
WHERE cfgapprovaltype.itemtype_uid = '63DF2CF7B1224152BDA9EA37ECF16F4D'
And the following is the IO when I run it separately:
(1 row(s) affected)
Table 'ApprovalWorkflow'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CFGApprovalType'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
However, when I run sp_WhoIsActive I see that it has been running for an fifteen minutes to an hour. It has a sleeping status. Also, it is part of transaction.
This query is really basic. I am wondering if there is anything that I can do because it seems that the application (ERP) maybe opens a transaction and doesn't commit it and then that query is stuck holding onto those resources.
Also, in sp_WhoIsActive returns that it has read close to 25K pages.