SQL performance

This talk was created because of persistent misconceptions that virtualization is unfit for and hurts the performance of SQL RDBM's (though this talk is about MS SQL a lot of it can be generalized to be used for other IO intensive workloads as well).







Problem is a reduction in storage spindles is one source of performance problems. Queues can also be a problem.

Sequential reads drop when there are more hosts added to your vmfs volume.





One way to fix this is to set memory reservations.





This pitfall is someone using the hosted product and creating a misconception about ESX.

Newer procs are always better. Decrease in pipeline lengths and increase in caches.





Dell's DVD store is a great benchmarking tool.









Servers that had multiple SQL instances are best broken down in multiple VM's.

When doing PoC's ensure you use target HW for correct modelling.

There is a white paper on VMware.com about this topic.

CapacityIQ

The obligatory statement that what's being discussed is conceptual and may not be the same as the end product starts off the talk..





The problem this tools tries to address; Basically we tend to overprovision because we don't have the right tools to predict better.

Virtualization obviously helps in this area.








There are multiple considerations in virtuality
- resource dependencies
- workload mobility
- Cpu and memory optimizations (memory ballooning)
- storage optimizations (thin provisioning linked clones)





The key is that the envirornment is dynamic.

Again SLA's are key and need to be guarded.




The architecture:












Do what if scenarios. Capacity dashboard





You can set alert triggers, fi send me an email when capacity reaches 80%.





Example of a whatif report




The focus is on intelligent planning using trending and it gives tools to optimize your current environment by identifying idle VM's f.i.



How the products work together.

Virtualization Optimization Capacity Mgmt best practices SAS institute

This session was on best practices re: virtualization Capacity Planning, as per experience of SAS institute.

Some of their findings:
- Memory is more important than CPU
- Memory tracking is important

They started very conservative (as most when new to virtualization) and started with a ratio of 11:1. They then used the SAS tool to track performance and behaviors and from the statistics were able to gather that they weren't using their virtual infrastructure optimally. They the determined that they could increase their consolidation ratio; from 11:1 to 20:1.

The tool name is: SAS IT intelligence for VMware VI.

Along the way they also discovered that the CPU ready measurement is an important measurement to track.