SQL and the Reporting Engine
Epicor ERP 10 requires two primary SQL functions, the SQL Engine and the Reporting Engine, also known as SSRS – SQL Server Reporting Services. Each require a SQL licensed, but if you run both on the SAME operating system instance, you can pay for only one set of licenses. If you run the SQL Engine on one OS and SSRS on another, you must purchase TWO sets of licenses – an expensive choice. Therefore, most clients choose to run SQL and SSRS on the same OS.
This co-existence can be a problem when one or the other get resource hungry and crowds the other out. For example, SQL (the engine) will use ALL available RAM, and starve any other application (sometimes even the Windows OS itself!). Once of the tuning options we set is to limit SQL RAM to approximately 80% of the total server RAM. If SSRS is running on the same OS, then we also need to leave some room for it to do it’s job.
Another resource grab is for the CPU, if a poorly written SSRS report is let loose, it can take too much time and CPU and starve the SQL Engine for CPU cycles, effectively driving CPU utilization to 100% and before you know it, SQL is s-l-o-w, and Epicor responds in kind. Users call up saying Epicor screens are frozen, reports are queueing up and not running, in general, the business comes to a full stop.
Looking at Task Manager / Details and seeing SSRS service “ReportingServicesService.exe” at 100% is a dead giveaway. The quick fix is to restart that service. Resources are released and SQL engine gets the horsepower it needs to keep running. Unfortunately, the currently running reports fail.
Microsoft used to include a function called “Resource Manager”, but they phased that out a few versions ago.
The best solution is multi-pronged attack, take your pick
- Split SSRS off into its own server (don’t forget to buy the license!)
- Monitor CPU utilization on your SQL servers and alert someone if they get pegged at 100%
- Create a Performance Monitor Alert and Task to restart SSRS if it goes to 100%
- Ensure any problem SSRS reports are debugged on a test server where they won’t impact production performance.
- Use a 3rd party tool called “Process Lasso Server Edition” from www.bitsum.com to force SSRS to behave.
In our EstesGroup Cloud Hosting ECHO hosting model, we use a combination of these solutions to ensure your Epicor ERP 10 system stays responsive.
If you liked reading the “Ninth Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.
