Best Practices for Virtualizing SQL Server

31 05 2009

I was at a conference this past week attending a session on server virtualization which included several different interactive polls regarding what the audience was doing with virtualizing systems in their own environments.  A common theme of course was Test/Dev being the obvious consideration and to no surprise there were several people who still doubted that virtualizing I/O intensive workloads such as MS SQL is a good idea.

This made me think of a presentation I had the pleasure of sitting in on while at EMC World a few weeks ago led by Scott Drummunds from VMware.  Scott, Manager of Technical Marketing, did an excellent job of reviewing the following common pitfalls and recommended tuning tips for mitigation.

Common SQL Virtualization Pitfalls

  1. Storage– It’s probably not much of a surprise that the most common pitfall is misconfigured disks.  Most of the time the disks associated with ESX datastores are configured for size and not performance, and consolidation tends to surprise admins in terms of the total number of physical disks needed.  Along with the number of disks no consideration is given to sequential reads getting shuffled as the number of hosts increases.  As the number of hosts spread across a single datastore increases it tends to generate a large amount of random access.  To avoid this pitfall be sure to allocate the same number of physical disks to a workload as would be allocated if the system was still physical.  This is a good starting point but it is still critical to test and verify the demands of a specific system.
  2. Guest Memory Management– Memory over-commit at the host level or memory provided through tuning to circumvent 32-bit limitations can cause adverse performance issues.  If more than 3GB of memory is needed for SQL consider using a 64-bit OS and corresponding version of SQL.  Be sure to set memory reservations for SQL VM’s at the host level especially if memory is being over-committed.  Set ‘Min/Max memory’ within SQL per MS best practices.  Last, if ‘Lock Pages in Memory’ is set be sure to again reserve memory for the VM mirroring the same amount recognized within the OS.
  3. Use the Correct Version of VMware– It seems all too obvious but you would be surprised by how many people try to use VMware Server, referenced as a Type 2 hypervisor, to virtualize critical workloads.  While a Type 2 hypervisor, referencing the industry definition, is not always inferior it can introduce certain bottlenecks not present with a Type 1 hypervisor such as ESX.
  4. Misunderstood Benchmarks– Everyone including myself has been guilty of incorrectly interpreting a performance benchmark.  If a system is critical enough then it should warrant executing a performance test in your environment on the exact system where the deployment is planned to reside.  There are several free and inexpensive tools available, such as Dell’s DVD Store, TPC-E, Benchmark Factory, etc, which can facilitate a load test which will valid your specific configuration.
  5. Design Limitations Outside of the Virtualization Layer– General SQL design best practices should be followed regardless of whether or not the server is physical or virtual.  This includes running a single instance of SQL on a individual server for very performance intensive workloads.  While this comes at a higher price it can improve performance and ensure issues such as runaway transactions will not impact several different databases in your environment.
  6. Alignment of Disk Partitions– This one should technically be included in the Storage bullet point but is so commonly overlooked that it deserves specific mention.  This topic has been beat to death on other blogs so I’m not going into a ton of detail but be sure to follow MS recommended best practices based upon your OS version and partition size to correctly align disk partitions for better performance of your storage.

Other Common Considerations:

  • LPE : Large Pages enabled in guest
  • PB: SQL Priority Base within Enterprise Manager/Management Studio
  • Ensure current drivers within guest system
  • Transmit Static Coalescing within ESX
  • Individual LUN’s for SQL DB files (Separate disks for OS, DB, Logs)
  • Leverage DRS as there have been several interesting studies which outline better overall performance with allowing the system to load balance workloads as apposed to human intervention

As stated there is a ton of information regarding this topic located elsewhere on the web so I encourage further research starting with the following resources:

http://communities.vmware.com/docs/DOC-8964 – Best Practices for SQL Server

http://www.vmware.com/pdf/SQL_Server_consolidation.pdf – SQL Server Workload Consolidation

http://www.vmware.com/files/pdf/SQLServerWorkloads.pdf – SQL Server Performance in a VI3 Environment

http://www.vmware.com/files/pdf/benchmarking_micrsoft_sql_vmware_esx_server_wp.pdf – Benchmarking SQL Server on ESX 3.5

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: