Dealing with SQL Blocking in Our ERP System
Asked by
TechWhiz101
I've just landed a job as an IT Support Specialist, but it turns out I'm really handling everything from system and database administration to network management. I started working with SQL just yesterday, and I'm already facing a frustrating issue with our aging ERP system, JobBOSS. It seems that when multiple users are accessing the system at once, it freezes up completely. After diving into the SQL server, I discovered that tables are getting locked, which is causing the problem. I tried creating a nonclustered index on one of the problematic tables hoping to resolve it, but more tables keep getting locked as I monitor. I suspect I might need to enable Read Committed Snapshot Isolation (RCSI), but being new here, I'm really anxious about making changes that might break something. I even shared a summary of the situation using a privatebin link, just to provide a clearer picture.
Answers
It sounds like the vendor needs to get involved since they’re the ones who can effectively address issues with their application. Also, it’s important to clarify what you mean by "locked" in this context. That can help in diagnosing the issue better.
Reply to this answer
Your observation about the blocks and locked tables definitely makes sense. Enabling RCSI could be a good move, but ideally, you’d want to get the vendor's approval first. In the meantime, consider boosting your storage speed with SSDs or increasing memory if you haven’t already. And welcome to the joys of dealing with subpar ERP systems—this one even needs Microsoft Office and Crystal Reports to function!
TechWhiz101: I completely feel you! This system is a nightmare. I even managed to convince my boss to consider switching to Odoo if we can demonstrate that proprietary does not mean less secure. I called the vendor, and all they said was to upgrade, which I doubt will actually solve our problems.
Reply to this answer
Have you checked the Current Disk Queue Length when these issues occur? From what you shared on the pastebin, it looks like blocking during updates is somewhat normal. While blocking is part of SQL operations, it should be quick. Tackling this issue safely will likely require some further analysis on your part.
TechWhiz101: I’ll definitely check that again. Earlier, I ran some SQL queries for I/O checking, and it seemed fine. But the freezing issue really starts to kick in during blocks, sometimes freezing for up to 30 minutes, which is just unacceptable!
Reply to this answer
Add your answer
Answers are reviewed before publication.
TechWhiz101: Yeah, unfortunately, our vendor contact is on vacation right now. I've posted that pastebin I mentioned, so hopefully, that provides some clarity.