Maximizing SQL Server Virtualization Performance Michael Otey Senior Technical Director Windows IT Pro SQL Server Pro Event Title 1 What this presentation covers Host configuration guidelines CPU, RAM, networking and storage Guest VM configuration guidelines VHDs, SQL Server storage configuration Using SSDs with SQL Server VMs Using In-Memory OLTP Software Defined Networking and Virtualization Event Title 2 Host CPU Guidelines If possible optimize performance through 1:1 ratio of virtual to logical processors (cores) Don’t overcommit the physical CPUs Ability of processors can limit the maximum throughput of virtual device (virtual NICs) More cores = more performance per VM Maximum SLAT vCPU limit can vary by guest OS enabled processors improve virtual machine performance Event Title Host OS Configuration Guidelines Get Windows Server 2012 R2 vSphere 5.5 Plan Event Title 4 the latest hypervisor for Scalability NUMA DVMQ vRSS Host Configuration Guidelines Hyper-V Host Configuration Plan for 1GB+ memory reserve for the management OS in the root partition Hot-Add RAM enables upgrades w/o downtime Plan for one dedicated NIC for management purposes Plan (ideally) for one dedicated NIC for live migration Separate LUNs/Arrays for hosts and guest OS VHDs Exclude VMs from AV scanning Event Title Guest Configuration Guidelines Hyper-V Provide adequate memory for Hyper-V guests Event Title Guest Configuration Paging, disk I/O cannot compete with RAM Avoid over allocating physical memory (overcommit) Fixed-sized VHDs for Virtual OS Install Integration Services / VM Tools Hyper-V Fixed Virtual Hard Disk Performance Overhead Analysis Native vs. Fixed VHD IO Performance Analysis Disk IOs per second (IOPS) (Window 2008 R2 Physical Disk(Native) vs. Fixed VHD(VM), ESG Lab Iometer workloads) SQL Log OLTP Database Physical Disk (Native) Event Title File Server Fixed VHD (VM) Exchange DB SQL Server and Dynamic Memory 12GB 12GB Live Migration 1 1 VS. 7.5GB 1 7.5GB Live Migration Host cluster Host cluster Shared Storage Shared Storage iSCSI, SAS, Fibre 1 iSCSI, SAS, Fibre • Event Title Remember SQL Server VM Storage Guidelines Change the default data and log files placement By default SQL Server puts everything on the drive with the SQL Server binaries Separate the data and log files to different VHDs Place the log file on fast writing drives -- RAID 1 or 10 Event Title 9 Best Practices for tempdb Don’t put tempdb on the same VHD as your data and log files Use RAID 10 is possible Data Event Title 10 files per core SQLCat - one data file per core Data files equal to 2:1 to 4:1 the number of logical processor cores SQL Server & SSDs CPUs have become faster Memory has become cheaper Disks have gotten larger but not much faster – I/O bottlenecks Simpler than SAN – SSDs can be dedicated to SQL Server Move Event Title 11 VHDs to SSDs SQL Server & SSDs SSDs disks Event Title 12 are much faster than rotational 15K rpm SAS drive can do about 150200MB/sec of sequential throughput 6Gbps SATA or SAS solid-state drive can do about 550MB/sec of sequential throughput 100,000 IOPs random read and write SQL Server & SSDs Short lifecycle? There is degradation with use High I/O implementations like SQL Server do shorten the lifecycle Drives will last years under normal usage Event Title 13 More with fuller drive Not forever Tops out at 5 yrs – SQL Server implementations will be less Don’t ever defragment SSDs SQL Server & SSDs No silver bullets Won’t fix slow queries Won’t fix a lack of memory Best for heavy random I/O Not so great for sequential Keep Event Title 14 utilization at under 75% SQL Server and SSDs Possibilities Move data files onto SSDs Move log files onto SSDs Move indexes to SSDs Move tempdb onto SSDs NAND not always the best solution Event Title 15 Too much write – fast burn out DRAM is optimum Fast write – built for pure random IO Expensive SQL Server 2014 In-Memory OLTP New In-Memory OLTP engine Formerly code named Hekaton SQL Server 2014 Enterprise Edition Only Moves New select tables and SPs into memory optimistic lock-free design Physical Event Title 16 or VM With enough host processing power and RAM Amount of host RAM is critical Software Defined Networking Virtualization Flexibility Speed application deployment Eliminates the need for VLANs Predictability High Event Title 17 for the network layer Maintain SLAs density networking Ensures VMs get the bandwidth they need [email protected] @michael_otey Event Title 18