Nov
30

Top 5 Performance Tips to Virtualizing Microsoft SQL

1.       Spread the Wealth

Try to keep similar workloads spread out on your vSphere Clusters.  Mix and match high Compute programs with your high disk I/O programs.  This way you have less of a chance of starving your resources. Inside your VM you should still split up database and transaction logs as you would in a physical world. Separating the random I/O patterns(data) and sequential I/O patterns (logs) will help your storage system out.

2.       Adjust the Belt Notches

You can achieve higher network throughput by increasing the transmit coalescing size.  Editing the value of Net.vmxnetThroughputWeight from 0 to 128 will help at the cost of increasing latency.  The setting can be found under the Software settings, advanced settings, then Net. Transmit coalescing is available on the VMXNET2, VMXNET3 and 1000g adapters.

 3.       If You Got, Flaunt It

Use the PVSCI adapters. The PVSCI adapter is more efficient because it allows for batching of I/O requests while the hypervisor is looking for more work. VMware has ran tests and found a 6% percent increase with I/O  throughput over the LSI adapter.  I would also add separate PVSCI controller for your data volumes and log volumes. The PVSCI adapter had a caveat that your workload should be over 2,000 IOPS or you may see a increase in latency. With the release of 4.1 now you can use the adapter under either small or large I/O workload.

 4.       If it’s locked,  it must be good

Any time you can stop paging to disk is good thing. You can prevent Windows from paging out the buffer pool to disk by locking the buffer pool memory .

http://support.microsoft.com/kb/918483

If you are going to do this make the balloon driver doesn’t kick in and defeat everything you are trying to do.  If you make sure all the RAM is reserved you shouldn’t have any problems.

 5.       Go Big or Go Home

Using Large Memory pages can reduce the overhead for your ESX servers.

You can see how to get it going at http://blogs.technet.com/b/sql_server_isv/archive/2010/11/30/trace-flag-834-and-when-to-use-it.aspx

There are lots gotchas so read the article and make sure you have a newer processer that has hardware MMU.

The idea is to have a TOP 10, I will add more points when time permits.

-Dwayne Lessner

Speak Your Mind

*