Identifying SQL Server Performance Issues in Epicor ERP
You have Epicor ERP running on the latest version of Microsoft SQL Server and everything is working great for months, but one day you start to notice that it’s taking you longer to login to Epicor ERP, or that your queries aren’t as fast as they once were. What’s happening? Is there a bottleneck? Is SQL Server slowing you down? Or, is your performance issue related to Epicor ERP?
Knowing what to look at first can be a challenge, but with Epicor ERP being optimized for the latest version of SQL Server, chances are the issue is with SQL Server, and not with Epicor ERP, so lets take a look at SQL Server.
The speed of your SQL Server can be impacted by many variables, including hardware, software, concurrent users, transaction volume, business processes and more. The most common bottlenecks causing SQL Server performance issues in Epicor ERP are typically related to CPU’s having insufficient hardware resources, memory, network, I/O bottlenecks and slow running queries (SQL Server hogs).
Monitoring and proactively staying ahead of SQL Server performance issues in Epicor ERP can help minimize their occurrence and lessen their impact. However, when an issue does occur, depending on the severity of the issue, your Database Administrator (DBA) or consultant may need to work quickly to identify the source of the problem so he or she can come up with a quick resolution.
When working with clients that are experiencing SQL Server performance issues in Epicor ERP, I first work towards identifying the problem by taking them through a series of steps:
Step 1: Identify and Quantify Speed Issues.
- How long does it take to log in?
- How long does it take to open a screen?
- How long does it take to bring back a customer record?
- How long does it take to bring back a book listing report?
- Do these results differ if run directly on the server and or workstation?
- Are all workstations speeds consistent?
Step 2: Run Server Performance and Diagnostic Tools
Run through Server Performance and Diagnostics Tool launched from Epicor Administration Console to test SQL Server performance issues in Epicor ERP. Record the speed.
Step 3: Run Script
Run the following script on the SQL server to determine if SQL server is truly the issue:
DECLARE @Loops INT SET @Loops = 1
DECLARE @CPU INT SET @CPU = @@CPU_BUSY DECLARE @StartDate DATETIME
SET @StartDate = GETDATE()
WHILE @Loops <= 1000000
BEGIN
IF COALESCE(‘123’, ‘456’) = ‘456’
PRINT 1
SET @Loops = @Loops + 1
END
PRINT ‘COALESCE, both non-NULL’
PRINT ‘Total CPU time: ‘ + CONVERT(varchar, @@CPU_BUSY – @CPU) PRINT ‘Total milliseconds: ‘ + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) PRINT ”
GO
Step 4: Make System Changes
Make changes to your system and test if you are experiencing the same SQL Server performance issues in Epicor ERP or if the results changed.
Conclusion and Special Thanks
A special thanks to our in house Epicor ERP consultants that contributed to this blog post. MIS has had great success in improving SQL Server performance for our clients using these steps. If you are experiencing performance issues, or if your system is performing ok, but not quite as well as it used to, give MIS a call and one of our consultants can see what can be done to help you get your SQL Server running at peek performance once again. If you found this article useful, please share on LinkedIn or Twitter.