Kyle Willman posted on October 14, 2009 14:03
Increasing the Performance of Time and Expense Entry for Business Portal 3.0 in MS SL
We recently assisted a large Microsoft Dynamics SL 6.5 client that utilizes Business Portal 3.0 to enter time and expense information. They were experiencing significant performance issues while entering this information. They engaged us to correct these issues. After an initial fact gathering discussion with the client, we uncovered the following information:
- The client has approximately 2,000 project employees entering time and expense information all on the same day.
- Every two weeks, when the server is experiencing its heaviest volume, they encountered issues with response time and intermittent errors associated with the Business Portal.
- At peak volume, the server would become extremely slow and pages would time out.
- At peak volume, users would experience intermittent DBNETLIB errors when performing basic functions in Business Portal.
- After restarting IIS or rebooting the Business Portal server (sometimes several times throughout the day) the server would perform slightly better and ultimately the problems would resurface.
Once we gathered all of the information that we needed to assist the client, we went to work monitoring the server and evaluating the software for potential bottlenecks.
We first utilized the Performance monitor to gather baseline performance statistics of the server during a period of light load. We then used the same performance counters to monitor the server during their highest traffic volume. After comparing the results of these analyses, we noted regular spikes in server memory usage during peak volume. Using these results, we recommended increasing the available RAM on the server from 1GB to 2GB.
While analyzing the performance metrics, we noted an irregularity in the IIS application pool setup. The application pool was scheduled to recycle every 60 minutes. The default IIS setting is 1740 minutes (29 hours). For normal web applications with no known memory leaks, we felt that this setting was extremely low and was likely contributing to the DBNETLIB errors that the client was experiencing. Every time the application pool was recycling, database connections were being dropped and resulting in this behavior. We recommended reverting back to the default IIS settings.
Finally, we scanned the Business Portal code for any obvious inefficiency in their most heavily used screens (Time/Expense entry). While evaluating the standard store procedures that provide the data to the screens, we re-wrote the stored procedures to optimize performance limiting the data returned in two ways:
- Time Frame: We limited the stored procedure to only return 6 months of historic time/expense entry records. (Before optimization the procedure returned approximately 7 years).
- Fields Returned: We limited the stored procedure to return only the four fields necessary to process the page. (Before optimization the procedure returned all fields in the table).
As a result of our optimization efforts, the client has experienced drastic improvements in their Business Portal performance. A small amount of analysis and effort has paid huge dividends to the client and confirmed that Business Portal is the best solution for their time and expense entry needs for their large user base.