maximizing sql server virtualization performance · sql server vm storage guidelines change the...
TRANSCRIPT
Event Title
Maximizing SQL
Server Virtualization
Performance
1
Michael Otey
Senior Technical Director
Windows IT Pro
SQL Server Pro
1
Event Title
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
2
Event Title
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 vCPU limit can vary by guest OS
SLAT enabled processors improve virtual machine
performance
Event Title
Host OS Configuration Guidelines
Get the latest hypervisor
Windows Server 2012 R2
vSphere 5.5
Plan for
Scalability
NUMA
DVMQ
vRSS
4
Event Title
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 Guest Configuration
Provide adequate memory for Hyper-V
guests
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
Event Title
Hyper-V Fixed Virtual Hard Disk
Performance Overhead Analysis
SQL Log OLTP Database File Server Exchange DB
Native vs. Fixed VHD IO Performance Analysis (Window 2008 R2 Physical Disk(Native) vs. Fixed VHD(VM), ESG Lab
Iometer workloads)
Physical Disk (Native) Fixed VHD (VM)
Dis
k IO
s p
er s
eco
nd
(IO
PS)
Event Title
SQL Server and Dynamic Memory
Shared Storage iSCSI, SAS, Fibre
Live Migration
1
Host cluster
12GB
1
12GB
Shared Storage iSCSI, SAS, Fibre
Live Migration
1
Host cluster
7.5GB
1
7.5GBVS.
• Remember
Event Title
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
9
Event Title
Best Practices for tempdb
Don’t put tempdb on the same VHD as
your data and log files
Use RAID 10 is possible
Data files per core
SQLCat - one data file per core
Data files equal to 2:1
to 4:1 the number of
logical processor cores
10
Event Title
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 VHDs to SSDs
11
Event Title
SQL Server & SSDs
SSDs are much faster than rotational
disks
15K rpm SAS drive can do about 150-
200MB/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
12
Event Title
SQL Server & SSDs
Short lifecycle?
There is degradation with use
More with fuller drive
High I/O implementations like SQL Server do shorten the lifecycle
Drives will last years under normal usage
Not forever
Tops out at 5 yrs – SQL Server implementations will be less
Don’t ever defragment SSDs
13
Event Title
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 utilization at under 75%
14
Event Title
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
Too much write – fast burn out
DRAM is optimum
Fast write – built for pure random IO
Expensive
15
Event Title
SQL Server 2014
In-Memory OLTP
New In-Memory OLTP engine
Formerly code named Hekaton
SQL Server 2014 Enterprise Edition Only
Moves select tables and SPs into memory
New optimistic lock-free design
Physical or VM
With enough host processing power and RAM
Amount of host RAM is critical
16
Event Title
Software Defined Networking
Virtualization for the network layer
Flexibility
Speed application deployment
Eliminates the need for VLANs
Predictability
Maintain SLAs
High density networking
Ensures VMs get the bandwidth they need
17
