2

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.

5
  • If there are open transactions from the app, no matter what you do, you will run into issues. Identify open transactions and work with the vendor/app team to sort that out. Blocking is an effect and not the cause.
    – Kin Shah
    Commented Jan 24, 2018 at 16:28
  • Thanks - I am working with the vendor now but they are pretty slow. Wondering if there anything else I can do. I don't think it is that first query though that I posted. I can kill that session an everything opens up but probably that session is holding onto something else that another user needs. It is a bit trickier to troubleshoot. Commented Jan 24, 2018 at 16:31
  • 1
    Try running sp_BlitzCache to see if you can find the query plan and explore that. This could give you an idea of why your query is "hanging", and what it is hanging on (e.g. scan, or sort or hash match etc.). The query should stand out by a mile if it's causing a horrendous amount of reads (which could be causing the hang). We had similar problems with our ERP system, it boiled down to bad bespoke work, and "inefficient" application design... that's the trade off of using an Enterprise level ERP system!
    – DimUser
    Commented Jan 24, 2018 at 17:01
  • Thanks! Will give that a go. The issue is that the main blocker's query is not complicated at all and I can run it in a few seconds. However, when I kill that session the rest of the app flows through fine. Just wondering if it is because it is part of a transaction that is open or not. is there a way to figure out what other queries are running under a transaction? Commented Jan 24, 2018 at 17:03
  • is any of the code from the ERP system parametrised? and when you run the code, I presume you are using hard-coded values? If so, then you Sir could be the "victim" of the wonderful "feature" of parameter sniffing! Try this bad boy, Slow in the Application, Fast in SSMS
    – DimUser
    Commented Jan 24, 2018 at 17:18

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.