Over the years, running SQL Server inside a virtual machine has changed from being a bit of a strange and risky idea to being commonplace and offering a range of benefits: more effective distribution of load, more flexible provisioning, and allowing additional tiers of redundancy to be put in place.
Though these factors are compelling, running your database inside a virtual machine introduces an additional set of variables to worry about. While a situation might look completely fine from inside the virtual machine, for example, other virtual machines running on the same physical host might have a dramatic effect on SQL Server performance by contending for the CPU, memory, network or physical I/O subsystem.
If you’re running SQL Server on a virtual machine, having data about the VM linked to other SQL Server metrics, is therefore essential when troubleshooting performance issues.
The SQL Monitor team has been looking into how you can more effectively monitor SQL Server running on a virtual machine, and we’re pleased to say that we’ve begun this by introducing support for VMware in SQL Monitor version 7.0.11. This is a beta version, which means what you see in this version won’t necessarily be in the final version.
We’ve tried to make getting started with monitoring SQL server running in VMware as straightforward as possible. Simply enter details of the vCenter, or the ESXi host if you’re running a standalone host, and SQL Monitor will automatically detect any SQL Server instances controlled by it. (You’ll need to convince whoever runs your vCenter to give you read-only access: they should be receptive to the argument that keeping track of the hardware SQL Server is running on is an essential part of your job.)
You’ll then see performance data from the virtual machine in the Server Overview page, with further metrics available on the Analysis page.
Let’s look at a few examples of how the new metrics available in SQL Monitor can help you diagnose problems that might come from SQL Server running in a virtual environment.
Firstly, here on the server overview screen, we can see a SQL Server instance taking up almost 100% of the CPU on the virtual machine, but the physical host’s CPU (indicated by the dotted line) is low. This helps you straight away: it suggests that your virtual machine might be under-provisioned and could be reconfigured with more vCPUs or with a lower CPU limit.
More metrics are available from the analysis page. For instance, here we can see ballooning, one of the techniques used by the VMware hypervisor to reclaim memory from virtual machines. It works by a fake device driver requesting memory from the operating system, which in turn prompts running applications to garbage-collect or otherwise release memory.
This can have a dramatically adverse effect on SQL Server performance: SQL Server typically takes as much memory as possible for its cache, and ballooning can force the operating system to page this cache out to disk.
As a final example, here’s the co-stop wait metric for a different virtual machine. This host-level metric essentially gives the amount of time that all virtual machines on the physical host had to wait for CPU resources to synchronize with each other to simulate parallel execution.
The normal recommendation is for this to be almost zero: the spike in the graph indicates a problem and probably means that the virtual machine is configured with too many virtual CPUs or the physical host is under significant load.
These are only three of the new metrics available in SQL Monitor. We’ve tried to give you the metrics that are most important to understanding SQL Server performance, and they’re all accompanied by comprehensive explanations of what they mean.
However, we are always looking to do more. As this is in beta, we’d love your feedback on this feature. We’ll be looking very closely at any suggestions and making some changes along the way. To make the feedback process easier we’ve added Intercom Messenger chat so you can talk directly to us (there’s a little icon that pops up when you’re using the new VMware parts of Monitor). You can also get in touch through User Voice.