SQL Server, SANs and Virtualisation

Question

SQL Server, SANs and Virtualisation

Viewing 9 posts - 61 through 68 (of 68 total)

You must be logged in to reply to this topic. Login to reply

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 1

george sibbald (1/21/2009)
a long thread here but I don't think this has been asked\answered -
the article mentioned that VM streamlines the DR process, but for that to be fully true the SAN would have to be mirrored as well, yes?
for what its worth we have hit performance problems on VMWARE, this was with a fairly small (20GB ) but heavily used database. I was not involved in the design of the solution (sigh) but I understand it is an up to date version of vmware but all vms on the box were sharing the same LUN, so the problem was likely the SAN set up?

Was the max degree of parallelism greater than 1 on your instance?

VMs do not handle parallelism well, from what I have heard. This could partly the problem here.

Jonathan Kehayas has actually mentioned this earlier in this thread.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

much as I dislike VMs I have never encountered turning off parallelism, that could be a serious hit and I'm pretty sure this is untrue. I guess some vms may be able to allocate virtual cpus or may leave HT turned on, in which case this is not the way to configure for sql server. In all the VMs I've used cores are allocated to each vm.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

george sibbald (1/21/2009)
a long thread here but I don't think this has been asked\answered -
the article mentioned that VM streamlines the DR process, but for that to be fully true the SAN would have to be mirrored as well, yes?
for what its worth we have hit performance problems on VMWARE, this was with a fairly small (20GB ) but heavily used database. I was not involved in the design of the solution (sigh) but I understand it is an up to date version of vmware but all vms on the box were sharing the same LUN, so the problem was likely the SAN set up?

We use SAN Replication for our DR. It isn't quite real time, but it isn't far behind it unless there is a lot of disk movement happening on the SAN at which point it will queue up until it either tips over and replication splits, paging our SAN Admin, or the activity slows so that the replication catches up.

Without a full picture of the Host, # VM's, types of VM's # spindles assigned to the LUN it is impossible to actually tell what was most problemattic for your particular example. Having a shared LUN is certainly somewhere up there in the list near the top. I got a email from someone last year asking how I could recommend SQL on VMware after they attended a session where I discussed this topic. They had 4 SQL Servers on a single host and were having huge performance problems. Turned out that they were using DAS in a RAID 5 with 4 300GB disks. There is no way in the world that you will get good performance with that configuration. I personally would have issues running that configuration for a single production SQL Server physically, let alone 4 of them virtualized on that hardware.

The up front cost to building a Virtual Infrastructure that is going to provide good performance can be very steep initally especially if you don't have a SAN, and if you are going from a bunch of small 1 or 2 way servers to a 4 or 8 way host type machine. Sure Virtualization is touted as a way to make better use of your existing hardware, but you need to take a serious look at what your existing hardware actually is, and then size that up with your actual expectations.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

colin Leversuch-Roberts (1/21/2009)
much as I dislike VMs I have never encountered turning off parallelism, that could be a serious hit and I'm pretty sure this is untrue. I guess some vms may be able to allocate virtual cpus or may leave HT turned on, in which case this is not the way to configure for sql server. In all the VMs I've used cores are allocated to each vm.

If you allocate cores to each VM directly, this isn't a problem, but then if you do a vMotion of the server, those settings don't follow the server. Even then, you have to manage this for all of your VM's on the host, or you still can have context switching issues between VM's. In our environment, this isn't something the server team is going to do consistently.

george sibbald SSC Guru Points: 104210 More actions · Answer 5

thanks for the replies.

The VM would have had either 2 or 4 cores (cannot remember for sure) with maxdop = 0, so parallel queries would have been possible.

We are using SRDF for SAN replication, just wanted to be sure without that the DR setup is not complete.

I don't know too much on the details of the VM builds, but will make it my business to find out., I know they average about 10 VMs per host.

I'm still getting to grips with this so excuse my ignorance, but due to the way the LUN is attached to the host it is not possible to failover a single VM in a DR situation, its the whole Host or nothing (well I am told it is possible but complicated so SAN guys not keen to do it and it would have to be done put of hours). therefore to get quick HA for a single SQL VM on the host I am having to also logship to another VM in the other data centre. Is this type of belt and braces approach typical?

---------------------------------------------------------------------

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Colin, i have never encountered changing the max degree of parallelism either.

george sibbald (1/21/2009)
the article mentioned that VM streamlines the DR process, but for that to be fully true the SAN would have to be mirrored as well, yes?

yes, ideally.

george sibbald (1/21/2009)
for what its worth we have hit performance problems on VMWARE, this was with a fairly small (20GB ) but heavily used database. I was not involved in the design of the solution (sigh) but I understand it is an up to date version of vmware

what version exactly?

george sibbald (1/21/2009)
but all vms on the box were sharing the same LUN, so the problem was likely the SAN set up?

all VM's sharing the same LUN? I'm guessing they werent all sql vm's? How many vm's and what type of services (file, application, sql)? As we know for performance you would want at least 3 separate storage areas for sql (OS, data and logs) thats without considering any othe rvm's that were hosted.

I would use processor affinity in VMWare with caution as cpu resource assignments could surpass the physical resources available, a classic example of this is virtualising a citrix server. Its also important to only use the minimum cpu resources and only implement SMP where it is actually used (SQL server for example)

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Jonathan Kehayias (1/11/2009)
One other thing to test for your larger database is the effect of parallelism waits CXPACKET on performance. My experience with VM's is that parallel processing is a performance killer rather than a performance enhancer. The reason for this is that the VM host software is configured to manage context switching and the multiple threads are no longer going against multiple processors necessarily. Generally speaking, I see much better performance from my VM's be setting the Max Degree of Parallelism to 1. If your 1TB+ database currently benefits from parallel processing, you definately want to test what the performance will be like under a VM.

Please make sure that you read my entire statement above. I am not saying to always set maxdop 1, but monitor it. If you find high level of CXPacket waits, then you are having parallel bottlenecking. The only servers I have ever had issues with this are virtual, and it generally on the larger 100GB+ databases. I rarely see CXPacket waits on servers with DB's that are small, no matter how many databases there are on the server. You have to test this to know for certain whether it affects you.

george sibbald SSC Guru Points: 104210 More actions · Answer 8

Perry, I'll find out the exact versions and LUN configuration if only because I want to know and am a little worried we might be missing a trick here.

I would be pretty sure most of the other vms would be application servers. We have not virtualised many SQL servers and those we have are small 3rd party apps that run quite happily on a physical server with a C drive for OS and an E for the databases, data and log and backup (with TSM to offsite). This one app of that type did not perform on vmware would be the busiest of these small apps we support.

I am quite interested in making use of virtualisation where possible, though I admit my main driver is the DR advantages it offers.

---------------------------------------------------------------------

Perry Whittle SSC Guru Points: 234013 More actions · Answer 9

george sibbald (1/21/2009)
Perry, I'll find out the exact versions and LUN configuration if only because I want to know and am a little worried we might be missing a trick here.

cool, this info would enable us all to give you a better opinion

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉