vPivot

Scott Drummonds on Virtualization

SQL Server Performance Problems Not Due to VMware

5 Comments »

[First re-post of an old favorite.  This document is my most popular blog entry from the communities.]

Microsoft SQL Server runs at better than 80% of native on VI3 in most benchmarked environments. In production environments, and under loads that model those conditions, SQL Server runs at 90-95% of native on ESX 3.5. I can say this with confidence despite a large amount of the industry’s skepticism because I’ve spent so much time on SQL Server in the past half year. I’d like to share some of my research on the subject and observations with you.

Two weeks ago my colleague Chethan Kumar and I presented on SQL Server in Cannes, France for VMworld Europe 2009. This presentation was the culmination of six months of investigation that was started at VMworld 2008 in Las Vegas. At that event I heard many customer concerns about SQL Server performance that I was resolved to identify the problems’ root causes. I talked with every customer I could find that claimed that SQL ran at anything less than 70% of native.  So many of these contacts claimed that they had measured SQL at 25% of native or worse that I knew that something was going wrong.

First, let me show you a slide that Chethan presented at the show in Cannes:

sql_tuning.png

Chethan spent three months investigating SQL Server to find out how much he could improve virtual performance from the “out of the box” experience. As this figure details, the sum total of performance improvements was 15%. Here’s another break-down of these results:

sql_tuning_summary.png

The only option that we found in ESX to improve virtual performance was static transmit coalescing, which is documented on page four of one of our SPECweb papers. Large pages and SQL’s priority boost, which are best practices provided by Microsoft for SQL Server configuration, provide the largest gains in performance.

The key messages that we communicated to our audience were that a properly running SQL Server should run at 80% of native or better. In most production cases it can run at a performance indistinguishable from native speed. And if performance is lagging, there don’t exist many changes that can be made to ESX that can yield and performance gains at all.

This begs the question: “If ESX can’t be tuned to double SQL performance, what is causing these reports of terrible SQL Server throughput?” The great majority of the problems are coming from mis-configured storage. But a variety of other items such as poor hardware selection or use of the wrong virtualization software contribute to the confusion, as well. I’ve been documenting these issues in Best Practices for SQL Server on this community and will continue to update that document as more problems are discovered.

If you have a SQL Server running un-virtualized in your environment, I’d like you to try virtualizing it again. Follow our best practices document and pay close attention to your storage configuration during deployment. I feel confident that once you’ve setup your environment properly, you’re going to like what you see.

5 Responses

Hi There, Did you get any people mentioning connection problems?
We have been having major problems with connections dropping between client and SQL Server databases across a range of applications (all since we moved to VMWare)
They appear to be random and think they could be when the Server ‘moves’ to new MAC address during normal working day.

  • The virtual machine will maintain is MAC address as it is migrated from one physical host to another. This should not cause drops. We have tested this extensively.

    Are you seeing any warnings or errors in your SQL logs?

    • We have no errors or warnings in our SQL logs. Our Monitoring team have told us last time we were complaining about connections dropping that it did seem to be at same time as virtual machine had moved according to VM logs.

  • Hi Phil, we have the same problem on ESXi 4.1: did you solve it?

  • Whats up, I assumed you could potentially be able to help me. I have a Microsoft SQL .ldf and .mdf file that I have to restore. Any ideas on how to do this? Take care