Wednesday, 4 November 2009

SQL 2008 on VMware

Microsoft SQL on VMware is not as simple if you want to get the best of it because there are a few more things to take into account when building the server.

So after looking at this I decided to put some other guide lines down for virtual server running Microsoft SQL, so here they are.


1) Priority Boost,By default, the priority boost setting is 0, which causes SQL Server to run at a normal priority whether you run SQL Server on a uniprocessor computer or on a symmetric multiprocessor (SMP) computer. I recommend you change this to 1 and this will cause SQL Server process runs at a high priority.


2) Enable large-page, Trace flag 834: Use Microsoft Windows large-page allocations for the buffer pool.

Trace flag 834 applies only to 64-bit versions of SQL Server. You must have the Lock pages in memory user right to turn on trace flag 834. You can turn on trace flag 834 only at startup.

Trace flag 834 causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool. The page size varies depending on the hardware platform, but the page size may be from 2 MB to 16 MB. Large pages are allocated at startup and are kept throughout the lifetime of the process. Trace flag 834 improves performance by increasing the efficiency of the translation look-aside buffer (TLB) in the CPU.


3) Disk Alignment, should be set 1024k and NTFS Allocation Unit Size.

When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.


4) If more than 3 GB is desired, use 64-bit versions of the OS and application.

VMware recommend this for all Microsoft SQL server versions.

5) change virtual disk heap, VMFS3 max heap size from 16MB to 64MB

This applies to older version of ESX server mostly.


And there you have it the 5 points to tuning you Microsoft SQL server on VMware, by the way the one thing I left out was regarding how to setup your SAN storage for best I/O, there was a reason for this related to the size of the posting needed to explain how to setup the storage best and because of the number of type of SAN devices on the market.