Best practices for SQL Server 2008 in VMware
- Set a fixed amount of memory that the SQL Server process will use. For example, set the max server memory and min server memory equal and large enough to satisfy the workload (2500 MB is a good starting value).
- Configure SQL Server to Use Soft-NUMA – To avoid any performance latency resulting from remote memory accesses, you should size a SQL Server virtual machine?s memory so it is less than the amount available per NUMA node. For more information about NUMA, you can visit http://msdn.microsoft.com/en-us/library/ms345357.aspx
Note: This should be applied only if it is applicable to your environment.
- If you set the SQL Server lock pages in memory parameter, make sure to set the virtual machine?s reservations to match the amount of memory you set in the virtual machine.
- Address Windowing Extensions (AWE) should NOT be used with any of the 64-bit platforms.
- Do NOT use priority boost option in SQL 2008 because this feature will be removed in a future version of Microsoft SQL Server.
- Use the VMXNET3 network adapter for optimal performance. VMXNET3 driver also supports jumbo frames and TSO for better network performance. To use the VMXNET3 device you must explicitly select VMXNET3 when configuring your virtual machine.
- Aligning file system partitions is a VMwarell-known storage best practice for database workloads. Partition alignment on both physical machines and VMware VMFS partitions prevents performance I/O degradation caused by I/O crossing track boundaries.
- For failover SQL Server clustering deployments, it is very important to use the eagerzeroedthick format when you create disks for virtual machines. By default, the vSphere Client and vmkfstools create disks in zeroedthick format. You can convert a disk to eagerzeroedthick format by importing, cloning, or inflating the disk. Disks you deploy from a template are also in eagerzeroedthick format.
Howto Set Locked pages for SQL Server 2008
- Install SQL 2008 the latest service pack
- Install the latest Cumulative Update if available
- Enable trace flag 845 as a startup parameter
- Ensure the service account for SQL Server has the Locked Pages in Memory privilege set. This is not required if the service account is LOCAL SYSTEM.
How to assign the Lock pages in memory user right
- Click Start, click Run, type gpedit.msc, and then click OK.
- Expand Computer Configuration, and then expand Windows Settings.
- Click User Rights Assignment, and then double-click Lock pages in memory.
- In the Local Security Policy Setting dialog box, click Add User or Group.
- In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
- Close the Group Policy dialog box.
- Restart the SQL Server service.