Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567

SQL Server, SANs and Virtualisation Expand / Collapse
Author
Message
Posted Wednesday, January 21, 2009 7:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 12:34 PM
Points: 1,865, Visits: 3,615
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.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #640725
Posted Wednesday, January 21, 2009 7:58 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
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.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #640748
Posted Wednesday, January 21, 2009 7:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:10 PM
Points: 1,683, Visits: 1,797
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 | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #640752
Posted Wednesday, January 21, 2009 8:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:10 PM
Points: 1,683, Visits: 1,797
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.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #640785
Posted Wednesday, January 21, 2009 10:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 5,879, Visits: 13,015
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?



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

Post #640921
Posted Wednesday, January 21, 2009 10:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 6,518, Visits: 14,038
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"
Post #640954
Posted Wednesday, January 21, 2009 11:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, October 20, 2014 3:10 PM
Points: 1,683, Visits: 1,797
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.


Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Post #641009
Posted Wednesday, January 21, 2009 12:29 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 AM
Points: 5,879, Visits: 13,015
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.


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

Post #641054
Posted Wednesday, January 21, 2009 1:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:28 PM
Points: 6,518, Visits: 14,038
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"
Post #641132
« Prev Topic | Next Topic »

Add to briefcase «««34567

Permissions Expand / Collapse