Q: How do you define a vCPU? Is it possible to allocate core or a socket to a VM (rather than a CPU thread - logical CPU)? Is NUMA configuration possible with VMs? Where do you draw the line between physical and virtual machines for database servers? We have a situation where, we have a application (in QA) both on physical and virtual machine. The one on virtual always has SOS_SCHEDULER_YIELD? Ant ideas, what could be the reason? A: vCPU is defined by clock speed (# vCPU * GHz). You can utilize NUMA if your hardware supports it. You can even use vNUMA for the guest but you need to have all the same hardware in your cluster compatiable in order to do it. A good reference for more information can be found at http://www.vmware.com/pdf/Perf_Best_Practices_vSphere5.5.pdf.
Q: On VSphere 5, CPU ready is in ms, so as a percentage of what is too high? A: When you are viewing the performance graphs, the graph updates every 20 second (or 20,000 milliseconds). With a ready time of 1035 ms. you can change it to a percentage by this calculation:
(1035 ms. x 100) / 20000 ms = 5,175%
To be able to interpret ready times, it is essential to know the relationship between the percentage of ESXTOP and ms of the Performance Graphs. You are seeing the same numbers. One is in milliseconds, the other is a percentage.
In general, you want to see virtual machines with a ready time lower than 1000 ms or 5%.
Q: On storage, how do we leverage SSD's with VMs? A: You can definitely use the SSDs to increase performance, specifically by using it as memory swap area. It will help ensure performance especially if memory is overcommitted. You also might want to move your tempdb / log file to SSDs.
Q: Do storage and log files need to be separated on Flash backed Tiered SAN storage, or will the SAN cache absorb the difference in access profiles? A: It is always a best practice to separate your data and log files for SQL Server instances. However, there are some storage tiering technologies which can dynamically move I/O intensive workloads to your Flash drives while the lesser I/O workloads are automatically moved to the lower performance drives.
Q: Since the LUN's are formed by multiple disks, how is isolating logs to a different drive better than not on the same in the same disk? A: Transaction logs, tempdb, and data files all have different I/O requirements and workloads that can conflict with each other. When setting up the LUNs (even though multiple disks are configured), special care should be taken to store like workloads. That said my percentage CPU ready is low (~3%) but CPU ready metric is about 150 ms, which is very high (with 15 VMs/host though). See the above calculation / discussion on this topic.
Q: We usually limit the RAM inside SQL Server. Does making a VMware RAM limitation make more sense? A: It's best practice to limit the RAM in SQL - however, the default is no limit so DBAs often forget to set it. If you are already limiting it in SQL Server, there is no need to set a limit on the VM. Just give it the limit size for the configuration of the VM plus extra for OS and other things.
Q: Support for XenServer?? A: SolarWinds Database Performance Analyzer (DPA) can monitor SQL Servers on XenServer but not with the VM option as we only have that available for VMware today. You can still monitor, you just won’t get as detailed information.
Q: We have a situation where we have an application (in QA) both on physical and virtual machine. The one on virtual always has SOS_SCHEDULER_YIELD waits? Any ideas, what could be the reason? A: Check to see if you have hyperthreading turned on as this may confuse the guest OS into thinking it has more resources available then it actually has. Also, check your CPU ready time as there is CPU pressure occurring.
Q: Degree of parallelism with VPUs—what’s your rule of thumb there? A: You should start with (number of vCPUs / 2 - 1), assuming you have at least 4 vCPUs. Also, be sure to bump up the cost threshold for parallelism as the default is 5. You can set this to 15 - 25 sp_configure 'cost threshold for parallelism',15;
Q: If I want to use local SSDs, does VMware supports TRIM command? otherwise performance will suffer, after time. A: Here is a blog which list commands equivalent to 'TRIM' or call 'TRIM' in the backgroup. Note the 'update' in the blog as the commands have changed if you are running vSphere 5.5. http://www.v-front.de/2013/10/faq-using-ssds-with-esxi.html
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community.
More than 150,000 members are here to solve problems, share technology and best practices, and directly
contribute to our product development process.
Learn more today by joining now.