carloscastilla - Fotolia

Manage Learn to apply best practices and optimize your operations.

Top four tips for virtualizing your SQL Server instance

The increasing need for SQL Server instances is raising costs. Basit Farooq shows you how to control the costs through effective virtualization.

Controlling the cost of databases and related hardware resources and improving and expanding them as necessary have become increasingly important. One solution to this problem is virtualization. Here are four key considerations to keep in mind when you're moving from a physical to a virtual environment.

Use SLAT-enabled 64-bit processors on the virtualization host

One of the most important steps for a SQL Server physical to virtual, or P2V, project is making sure that the virtualization host is using the right processor. A 64-bit processor is a requirement for a virtualization host, but not all 64-bit processors support Second Level Address Translation (SLAT). SLAT is a form of hardware virtualization in the current generation of Intel and AMD 64-bit processors. Intel calls its version of SLAT Extended Page Tables (EPT). AMD calls its version of SLAT Nested Page Tables (NPT) or Rapid Virtualization Indexing (RVI).

SLAT-enabled processors reduce the software overhead from shadow paging, which can become recursive and thus costly. SLAT is a mechanism for translating guest physical addresses to system physical addresses that leaves the guest in complete control over its page tables. SLAT activates on VM entry and deactivates on VM exits. SLAT-enabled processors improve VM performance and scalability. SLAT is supported on any AMD processors based on the Barcelona architecture or later and on Intel processors numbered E5500 and higher.

Keep a one-to-one ratio between virtual CPUs and physical processor cores

For better performance, maintain a one-to-one ratio between the total number of virtual CPUs (vCPUs) and the total number of physical processor cores. Doing so ensures that each virtual machine (VM) will have consistently available processing power.

Choose your virtualization platform carefully

SQL Server automatically takes advantage of multiple processors on your server. If the virtual SQL Server instance requires a high degree of scalability, choosing the correct virtualization platform can mean a significant difference in performance.

Since Hyper-V in Windows Server 2008 and 2008 R2 is limited to four vCPUs, there is a limit to the virtual SQL Server instances on those platforms. Windows Server 2012 raised this restriction by extending vCPU support to 64 vCPUs. VMware vSphere 5.0 supports 32 vCPUs, while the latest vSphere 5.1 supports up to 64 vCPUs. Use Windows Server 2012 Hyper-V or vSphere 5.0 or later for virtualized SQL Server environments entertaining high CPU-intensive operations.

Take advantage of dynamic memory and do not over allocate

Whether the server is physical or virtual, inadequate memory configuration can significantly affect SQL Server performance. In SQL Server, the buffer cache contains the data pages used recently, while the procedure cache contains T-SQL commands executed recently. The memory buffers allow SQL Server to get the data and commands from the data pages cached in memory. SQL Server automatically manages its memory buffers and adjusts them according to server workload.

However, excess memory allocation will help only to a point. In a virtual server environment, physical memory limits how many VMs can be active at any given time. When allocating memory to a virtual server, make sure to allocate only the amount of memory required for each virtual SQL Server instance. Over-allocating memory in one VM takes memory away from other VMs. Dynamic memory provides an answer to this memory problem.

For SQL Server to take advantage of dynamic memory, the guest operating system must be able to recognize hot-add RAM. To use dynamic memory with Hyper-V, the operating system, the Windows Server 2008 R2 SP1 or later, must be installed on the Hyper-V host. The guest operating system running on the VM must also support the ability to hot-add RAM. The following VM guest operating systems can use hot-add RAM: the Enterprise and Datacenter editions of Windows Server 2012, Windows Server 2008 R2 SP1, Windows Server 2008 SP2, and Windows Server 2003 R2 SP2.

To take advantage of hot-add RAM in the guest operating system, you need to be running the SQL Server Enterprise or Datacenter edition of SQL Server 2008 R2 or the SQL Server Enterprise edition of SQL Server 2012. SQL Server detects added memory use and can increase its memory to meet the workload demand.

Dig Deeper on Microsoft SQL Server Consolidation and Virtualization