SQL Server is a relational database management system developed by Microsoft, and it’s used for managing, storing, and changing data organized relationally. In some cases, you might want to use SQL Server on a virtual machine, but given the resource-intensive nature of the system, you need to ensure you set it up and manage it correctly. When virtualizing SQL Server, small resource inefficiencies can make a big difference.
There are tools on the market capable of helping you virtualize your SQL Server and keep it running efficiently. My recommendation is SolarWinds® Virtualization Manager (VMAN), as it provides comprehensive virtualization performance management alongside high-quality performance and capacity monitoring tools. You can also link it with other tools through the SolarWinds Orion® Platform, which can help you keep tabs on virtualization, SQL Server, and your overall network and IT infrastructure performance.
What Is SQL Server Virtualization?
SQL Server virtualization is the process of running SQL Server on a virtual machine (VM). It’s used to consolidate the number of physical servers you have running and reduce the number of SQL Server licenses you need to purchase.
It might seem like a good idea to set up a virtual machine to host as many processors as the SQL Server license allows—for example, you might want to configure SQL Server 2016 Standard to run a maximum of 4 sockets/24 cores and deploy as many database instances as possible. However, doing this incorrectly can create a huge amount of performance overhead, which can negatively impact your end users and your business. If you do it right, you should be able to efficiently use modern virtualization software with SQL Server.
The benefits of SQL Server virtualization are numerous. Many SQL Server deployments can benefit from virtualization in areas such as management, availability (including disaster recovery), and scalability. Additional advantages extend to server consolidation (and the accompanying reduced data center footprint), administration flexibility, and lowered total costs.
But to fully reap these benefits, you need to keep tabs on your SQL Server virtualization performance. Without proper management, your system can rapidly become inefficient and slow, impacting your dependent applications and services.
Common SQL Server Virtualization Performance Issues
Below are three common performance problems you might encounter with SQL Server virtualization:
- Overcommitting the Virtual Machine Host. If you have an overcommitted virtual machine host with numerous resource-intensive applications like SQL Server running on the virtual machines, this will almost certainly create performance problems. Overcommitting CPU resources can cause major issues.
- Accounting for Hyperthreading. Make sure you don’t take hyperthreading into account when figuring out how many virtual CPUs you have available for your SQL Server virtualization. When you use hyperthreading, instead of increasing performance, the two threads can compete for resources and adversely affect the performance of SQL Server. Any inefficiencies need to be accounted for with SQL Server virtualization due to its resource-intensive nature.
- Insufficient Memory/Overcommitment of Memory. Memory is also important for a well-running SQL Server virtualization process, as SQL Server consumes a lot of memory. Memory isn’t expensive, so you can easily invest in this resource to ensure you have enough. Remember, not having enough can have a big impact on your SQL Server performance. In addition, if you’ve overcommitted what you currently have, it can result in severe performance degradation.
You can avoid these issues through careful planning, proper hardware provisioning, and good organization.
SQL Server Virtualization Best Practices
There are several SQL Server best practices you can implement to ensure your virtual SQL Server instances perform as well as possible. By following these recommendations, you can eliminate the majority of your performance inefficiencies.
Use Updated Versions
First, keeping your hardware, hypervisors, and software updated is more important than you might think. Using older servers can make a big difference when virtualizing, as older processors simply don’t have the same level of power and performance as modern CPUs.
Modern CPUs all support second-level address translation, which helps the CPU maintain mapping between physical and virtual memory. If this isn’t performed well, the hypervisor tries to make up the shortfall, as mapping between virtual and physical memory is critical to VM performance. If this isn’t done well because you’re using an older CPU, VM performance will deteriorate, ultimately impacting SQL Server as well. Bottom line: ensure your hardware is up-to-date.
Second, make sure your hypervisor is updated. You should always use the latest version of Hyper-V or vSphere (or whatever you’re using). Newer versions have better scalability and higher maximums for capacity.
Manage Capacity
Another important best practice is to plan and manage capacity, including CPU and memory capacity. If you’re working with resource-intensive applications such as SQL Server, you need to ensure your VM is sized well and has the appropriate number of CPUs assigned to it. Make sure the number of virtual CPUs you’ve assigned doesn’t go above the number of physical CPU sockets available.
Additionally, make sure the CPU resources on your host aren’t overallocated. Overallocation can cause increased wait times, which can translate into SQL Server latency issues. This can then cause a cascade of slowdowns impacting the applications dependent on your SQL Server.
Alongside CPU capacity management, you also need to manage the memory available to VMs. Determine how much memory the VMs are actively using and adjust memory allocation to match. To do this, collect memory usage metrics and use capacity planning tools to model memory usage scenarios on the VM host. This can help you plan appropriately to figure out what your high-use scenarios are and ensure you have enough memory for those upper-range situations.
Perform Virtual Inventory
It’s important to have a clear record of your physical and virtual infrastructure. This includes a record of the virtual machines you’ve set up, where they’re hosted, and what software they’re running.
One risk of virtualization is the possibility of VM sprawl, which essentially means having far more virtual machines than you need. This can contribute to performance issues.
Maintaining a running inventory can help prevent sprawl. It can also help you easily troubleshoot, as you’ll be able to pinpoint any parts of your inventory that have faced problems or hosts coming to the end of their life.
Use Virtualization Management and SQL Server Virtualization Monitoring Software
My top choice for virtualization management software is SolarWinds Virtualization Manager (VMAN). VMAN comes with excellent performance management tools, including comprehensive dashboards designed to show your virtual server performance. You can use it with both VMware vSphere and Microsoft Hyper-V environments.
In addition, VMAN provides maps of your virtual machines, including their underlying hosts, storage, and related objects. This helps you determine virtual server performance issues and find links between the devices or connections potentially behind the problem. VMAN also provides information about your physical host, so you can check on hardware health and keep an eye on whether your hardware is causing performance issues.
The software’s dashboards give you a clear view of critical storage metrics along with performance metrics and alerts. These dashboards are customizable, so you can change them to suit your organizational needs. The basic metrics they can display include read/write, IOPS, and throughput. You can also see whether there are any storage contention issues at the cluster, host, or VM level.
VMAN provides historical data, making it easier to spot issues and where they might be stemming from. It can show you the history of your configuration data and infrastructure relationships, helping you quickly identify whether any dependencies have changed or whether a performance issue is rooted in a configuration or dependency in your virtual server environment.
A notable benefit of this software comes in the form of virtual server monitoring recommendations. When you’re busy with numerous other management tasks, it’s extremely helpful to get clear recommendations for managing and optimizing your virtual resources. These recommendations help you manage capacity, evaluate VM performance and usage, and pinpoint VM sprawl.
VMAN is built on the Orion Platform, which allows you to link the software with other useful tools, such as SolarWinds Network Performance Monitor and SolarWinds Server & Application Monitor (SAM).
There are other tools on the market, of course, but I highly recommend a professional solution designed for enterprise use. Tools capable of integrating with a broader platform are particularly useful, as they allow you to customize and expand your monitoring as needed. You can access a free trial of VMAN for up to 30 days.
For even more visibility into SQL Server virtualization, SolarWinds® SQL Sentry is built to give DBAs the performance views they need across their entire data estate, including virtualized SQL Server environments like Hyper-V and VMware.
The query analysis capabilities in SQL Sentry are designed to go far beyond what you’ll find in SQL Server Management Studio (SSMS), giving you the information you need to quickly resolve problems.
With SQL Sentry, you can:
- Monitor IO at multiple levels, including storage, operating systems, and databases
- Use the integrated Top SQL View to see detailed analysis that can help you more easily optimize queries
- Leverage customizable alerts to keep you aware of issues and potential issues
With a quick view of real-time and historical events and performance data, SQL Sentry shows execution plan diagrams, query history, and more, so you can find and fix potentially problematic queries. Download a free 14-day trial of SQL Sentry to try for yourself.
Key Takeaways
Using high-quality, enterprise-level tools such as SolarWinds Virtualization Manager is an important part of SQL Server virtualization management. Other best practices include maintaining an up-to-date inventory, planning and managing virtual machine host capacity, and keeping your hypervisors updated. Additionally, take care not to overcommit your physical resources. By following these recommendations, you’ll be positioned to benefit from the efficiencies of virtualizing SQL Server.