The IT department at one of our GP Clients made a decision to move SQL Server from a Physical Server to Virtual Host running Citrix XenServer. It was done to accommodate increase in disk space and memory requirements. The client has a decent transaction volume with over 3,000 SOP transactions a day, (8,000+ transaction a day during peak season) and 40 GP users. As soon as the SQL Server was moved to the new virtualized environment, GP users started experiencing delays in lookups and reports with GP locking up multiple times a day.

Here are the charts from client’s virtualization server that was used to host SQL Server for Dynamics GP. The CPU and memory utilization showed no real stress on the system. However, physical disk I/O displayed higher activity at the time of blocked queries. 

Figure 1: Virtualization Server: CPU Utilization Chart showing average 25% utilization

Figure 2: Blocked Queries reported by SQL Server

Figure 3: Physical Disk I/O on Virtualization Server showing Physical Disk Reads & Writes

Figure 4: Number of open sessions reported by SQL Server

First we tried to investigate the expensive queries, but did not find any queries that could be blamed for the performance delays. We also rebuilt indices to see if that would bring back the performance, but did not see much difference. We then analyzed I/O related performance counter which revlead that there were serious I/O problem with the I/O subsystem.

The major delays were coming from I/O operations. Almost all queries were waiting for WRITELOG or PAGEIOLATCH_SH which meant either IO system was not setup correctly or the server had more I/O requests than the hardware can handle. The resolution was to replace physical drives on the virtual host with SSD drives. As soon as the SSD drives were put in the performance improved drastically and GP freezes went away.

Figure 5: Charts showing Response Time, Locks, Active and Blocked Sessions

Figure 6: Chart showing I/O Waits during blocked sessions

CONCLUSION

In summary, there are various factors that affect SQL Server performance on virtualized envrionment. Moving SQL Server to a virtual machine with plenty of CPU power and memory does not mean that performance will be improved accordingly. Adding more memory and CPU Cores to the VM often provides a quick fix for performance issues but if the disk I/O is not configured properly it becomes the bottleneck to performance gains.

For a deeper look at GP performance on Virtualized SQL Server, stay tuned for the next article: 
How to Improve Microsoft Dynamics GP Performance on Virtualized SQL Server.