Your virtualization admin has been mandated to consolidate. Database admins are being mandated to improve performance. The DBA wants faster cores, while the virtualization admin wants more cores. All the while, neither of these admins understand each others environments, have a standardized way to test performance, or recognize how changes on one side affect the other. They do share one common business requirement though which is to spend less and do more with their shrinking budget. So how can we get these two admins working together to achieve mutual success? Here are a few tips.
It’s important for the DBA to have visibility into the virtual environment. Read-only view is typically enough to keep the virtual admin concerns about DBA world domination from occurring, and still allow the DBA to really understand where a bottleneck might lie in their environment. This will lead to a much more collaborative discussion when performance issues do surface.
With a virtual environment, the impact of noisy neighbors is much more likely. So be sure to stagger jobs accordingly. Do all of your backups and antivirus scans have to start right at midnight across the entire infrastructure? Can database maintenance be staggered as well across the multiple instances? In some cases, just placing your hosts across the environment approipriatly can help too. Your compute, network, and storage environment can each be segmented to prevent the impact of these noisy neighbors as well.
Right size your virtual environment. Sounds simple, but remember that virtualized environments are not the same as physical. For example, in a physical world, we frequently have to size for where we expect a workload to look like 3-5 years from today based on the end of life of that hardware. You get one shot at getting this correct and it’s hard to nail every time. In a virtual world, however, you have the luxury of making adjustments according to your workload requirements today. Understanding how CPU resource scheduling works is critical to ensuring you have the right number of cores assigned to your systems and you can find that information from David Klee at tinyurl.com/n42avu4.
Just as sizing your virtual environment is critical, connecting it to your resources properly is also critical. Use the fastest path to your storage that you can. Set the queue depth according to your vendors best practices. Use multipathing so that you have redundancy and load balancing. Most importantly, test stuff. Don’t just plug it in and walk away expecting it to work. Iperf is a very simple tool that you can use to make sure your end to end network connectivity is operating at peak performance. To test your SQL performance you can use Microsoft SQLIO and SQLIO Batch to automate testing that includes metrics for Read/Write with Sequential and Random IO, Multiple threads, and operations per second.
Speaking of storage performance, SQL is an odd bird because of the different profiles it has all running from the same environment. First of all they grow and change all the time. More data is added and the data that is being accessed is different based on the needs of the users. Your active working set is typically only a small fraction of the total database size. Virtualizing this workload consolidates all of this active and working data into a single path back to your storage. Enter Flash Driven Hybrid Storage. All flash is fast, but typically very expensive and in most cases overkill. Remember that only a small portion of the dataset is active. With hybrid storage, you can leverage the benefits of flash performance balanced with the cost savings of spinning disk. You can also scale IOPS and capacity INDEPENDENTLY of each other. This is crucial for the TCO of your storage environment. When you add inline deduplication to the array, you can drive more of the working set into cache providing even more performance.
In summary, virtualizing SQL can be a tricky proposition if you don’t do the right planning and testing. By leveraging the proper storage environment you can overcome many of the performance limitations found in traditional storage arrays, and make your applications even more response. In the end, responsive applications mean happy end users, and happy end users mean happy IT teams.
If you want to get more information, view this recorded webinar or download the slides from the following link: http://pages.tegile.com/2014AvoidingCommonSQLServerVirtualizationPitfalls_10SQLregpage.html
Digital IRL 