INTRODUCTION
Virtualization holds the promise of unlimited resources & computing power, and it has seen lot of success in virtualizing web servers, application servers, desktops etc. However, when it comes to running Microsoft SQL Server on a virtualized environment the results have been mixed. Whether you are using Microsoft HyperV, VMWare, Citrix XenServer or any other virtualization platform, SQL Server performance has been an issue especially for applications with I/O and resource intensive workloads.
PERFORMANCE ISSUES WITH MICROSOFT DYNAMICS GP
Microsoft Dynamics GP implementations for midsize to enterprise level organizations with 30+ users and more than 1000 transactions per day fall into this category of high I/O and resource workloads. SQL Server hosting Dynamics GP databases in such cases might experience delays in lookups, inquiries/reports as well as GP freezing or locking up from time to time. Performance monitoring on the SQL Server will reveal higher CPU or Memory utilization, or intensive Disk I/O activity, or all of the above.
REDUCING HIGH CPU UTILIZATION
In regards to CPU that has shown low utilization before, and suddenly starts showing over 70% utilization the culprit is most likely a badly written query. This could be easily fixed by identifying the query and re-writing it. On the other hand if CPU has historically shown high level of utilization then the issue could be lack of indices or need to rebuild indices. Looking up 5 to 10 most expensive queries via SQL Server Performance Report and adding appropriate indices can reduce the full table scans and improve performance. On a virtualized SQL Server we also have the option to increase CPU Cores but should be done after expensive queries and indices have be reviewed.
OPTIMIZING MEMORY USAGE
Memory is most critical factor affecting SQL Server performance. Having more memory on the system can reduce the number of I/O activity on the disk. The data that is queried is usually cached in memory. This keeps the SQL Server from running into performance issues. One measure to see if enough memory is available is Page Life Expectancy (PLE). If it’s lower than 200 then more memory should be added to the SQL Server. Another problem with SQL Server is the maximum memory allowed for SQL Server. If it’s left unlimited, SQL Server can end up with most of the memory not leaving enough memory and resources for other services and applications. To avoid this, maximum memory available for SQL Server should be set up to 80% of the total available memory.
ELIMINATING DISK I/O PERFORMANCE BOTTLENECKS
When running Microsoft SQL Server on Physical Hardware as opposed to a Virtual Machine (VM), performance issues related to Disk I/O can be mitigated by separating SQL Data & Log files to different physical disks. Microsoft Dynamics GP uses TempDB quite extensively, so moving TempDB to a faster drive can also provide performance boost.
However, when SQL Server is running on a VM inside a Virtual Host the Disk I/O issues are not easy to detect and resolve. This is because from inside the VM we might not get the full picture. We would need to look at the Disk Transfers/sec, Average Disk Queue Length, and % Disk Time on the Host to see if there are any I/O contentions. Furthermore, virtualization servers quite often are built for size (plenty of Disk Space, CPU Cores and Memory) and not performance.
PERFORMANCE ISSUES DUE TO QUERY BLOCKING
Whether you are running SQL Server on a physical or virtual machine the first step in diagnosing performance issues is to determine bottlenecks with most contention. These could be due to poor performance of a subsystem or improper tuning of SQL Server. One of the widely experienced symptom of performance delays is blocking in SQL Server which leads to Dynamics GP locking up on user workstations.
Blocking is caused by contention of resources. Most common reason for blocking is the lack of indexing or queries that do not utilize existing indexes. Blocking occurs when table is locked by an update or delete while another user is trying to read the data from the same table, or when a query performs a full table scan and takes a long time to execute blocking other users from accessing the table during that time. The blocking issues could be fixed by re-writing the queries, running the queries with (NOLOCK) option, or creating additional indices.
QUERY BLOCKING ON VIRTUALIZED SQL SERVER
However, if the Microsoft Dynamics GP is performing correctly on a physical machine and the blocking issues arise only when SQL Server is migrated to a VM then we have to look elsewhere. It is quite likely that some of the computation intensive queries are taking longer to execute on the VM as compared to a physical machine. We can certainly use SQL Monitoring & Diagnostic Tools to narrow down the queries that are causing the blocking and see if they could be re-written, granted that these are not encrypted or embedded inside Dexterity or .NET applications. A better and recommended approach is to review what other operations are taking place on the Virtual Host during the time the blocking happened. If there is enough CPU power and available memory then most likely the problem is disk I/O related.
Read the follow up to this article:
Disk I/O Recommendations for Virtualized SQL Server running Microsoft Dynamics GP