October 7, 2009 at 5:25 pm
My company are virtualising EVERYTHING. Anyway long story short - we have a piece of hardware running 2 virtualised servers. Lets call them A and B. When a stored procedure runs on A it KILLS B literally to the point it grinds to a halt. Very intensive stored proc I must admit.
Our network guys looked on the vmware console and tried to blame the sql config as the CPU useage (quad core) was not even and each core was at a different %.
I want them to set it so that if A needs resources it will get all but x amount so that it doesn't actually kill the server!
Anyway away from that - Its SQL 2K5 Enterprise 64 bit. I have not set a max memory. I don't think its running with "Lock pages in memory" set. Do you think these 2 settings would help?
October 7, 2009 at 5:44 pm
Virtualised SQL runs fine but you have to do a proper capacity planning for this. It's very important! You should turn off baloon driver for these sql servers too.
October 8, 2009 at 1:17 am
Marcin,
Could you expand upon what you mean by " You should turn off baloon driver for these sql servers too!" I am not overly familiar with this driver but what I have read seems to contradict what you are saying in that it is suggested you do not disable this driver.
Shark,
I assume both vm's are running off the same virtual host? Why can't your VMware guys reallocate resource accordingly?
I have since found this resource that discusses VMware resource management & the balloon driver. Which may be helpful/
http://www.vmware.com/pdf/vi3_35/esx_3/r35u2/vi3_35_25_u2_resource_mgmt.pdf
Mark
October 8, 2009 at 2:41 am
October 8, 2009 at 2:45 am
good luck with this, I have heard nothing positive about using Virtual machines for sql. as already mentioned you will need to play around with the config to get the cpu allocation correctly. as one instance can flatline the cpu as you have already found out.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 8, 2009 at 2:48 am
Shark Energy (10/7/2009)
My company are virtualising EVERYTHING. Anyway long story short - we have a piece of hardware running 2 virtualised servers. Lets call them A and B. When a stored procedure runs on A it KILLS B literally to the point it grinds to a halt. Very intensive stored proc I must admit.Our network guys looked on the vmware console and tried to blame the sql config as the CPU useage (quad core) was not even and each core was at a different %.
I want them to set it so that if A needs resources it will get all but x amount so that it doesn't actually kill the server!
Anyway away from that - Its SQL 2K5 Enterprise 64 bit. I have not set a max memory. I don't think its running with "Lock pages in memory" set. Do you think these 2 settings would help?
You dont mention the amount of memory that you have
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 8, 2009 at 2:54 am
Actually it may be 2 dual cores for what its worth.
Their is 8gb memory TOTAL shared between the 2 machines. Both machines are on the same host.
October 8, 2009 at 2:55 am
Thanks Marcin for the links... i'm sure they'll make interesting reading!
Silverfox, Our entire SQL farm (dev, test & Prod) are being run under VMware and I can honestly say we've never had any problems. For us anyway it's been an interesting cost cutting exercise.
October 8, 2009 at 3:00 am
Shabba (10/8/2009)
Thanks Marcin for the links... i'm sure they'll make interesting reading!Silverfox, Our entire SQL farm (dev, test & Prod) are being run under VMware and I can honestly say we've never had any problems. For us anyway it's been an interesting cost cutting exercise.
Thats really interesting Shabba, Can I ask what sort of systems they are running and the size of databases that you have. I take it you are running ESX ?
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 8, 2009 at 3:35 am
Thats really interesting Shabba, Can I ask what sort of systems they are running and the size of databases that you have. I take it you are running ESX ?
No problem... Yes we are using ESX 3.5
We are running SQL Server 2005 Standard Editon SP2 on 7 virtual machines Typically (2 Processor, 4-8GB RAM) Each server have between four and eight named instances installed which house approximately 275 databases, our largest database is just under 1 terabyte though typically db's are between 2GB - 150GB.
I appreciate that in terms of SQL db's these are quite small and probably fit well within this environment (Possibly why we haven't had any problems?)
October 8, 2009 at 4:34 am
Shabba (10/8/2009)
Thats really interesting Shabba, Can I ask what sort of systems they are running and the size of databases that you have. I take it you are running ESX ?
No problem... Yes we are using ESX 3.5
We are running SQL Server 2005 Standard Editon SP2 on 7 virtual machines Typically (2 Processor, 4-8GB RAM) Each server have between four and eight named instances installed which house approximately 275 databases, our largest database is just under 1 terabyte though typically db's are between 2GB - 150GB.
I appreciate that in terms of SQL db's these are quite small and probably fit well within this environment (Possibly why we haven't had any problems?)
Perhaps, the success of virtualisation is down to careful assessment and planning, as some applications and their needs do not scale up very well on virtual environments. I have been told that high end OLTP applications dont work very well on virtual environments and I would have concerns about multiple terabyte databases running on vmware but that is only my opinion. where i have seen vmware work well is on small-medium databases on web farms for instance.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 4:35 pm
Follow up question -
For performance on these 2 virtualised servers would I be best served getting my network guys to force A and B to have a 50/50 share of the memory, or force the memory share via Max Memory setting under sp_configure?
I did set A and B to have up to 6gb max thinking this would leave 2 gb for the other but obviously forgot about other resources to the 2 servers still kill each other. Although the worrying thing is that is just running 1 slightly resource hungry (but well optimised) query?!?!?!
If I instead set up 2 instances on 1 virtual would performance hit be worse?
November 6, 2009 at 7:33 am
Hi Shark,
If memory is definately the issue and your running at maximum, can you not add some more physical memory to the host and therefore assign more RAM to Server A? What are the hardware & VM spec's? Could you move this VM to another host? assuming you have multiple hosts of course.
If I instead set up 2 instances on 1 virtual would performance hit be worse?
Quite Possibly, take a look at configuring "Resource Pools" within your virtual environment. You can do this at host or at virtual machine level and both CPU & RAM is configurable.
Whoever manages your Virtual Infrastructure should be able to help. Otherwise checkout the VMWare forums (assuming your a VMWare site) or Microsoft.
November 6, 2009 at 7:50 am
I'm not sure memory is the issue now, but I did just adjust the Max Server memory.
I think it seemed to be the B server was hogging all the processor rather than memory or disk space. Why would this be?
November 6, 2009 at 11:04 am
It might be an issue with processor scheduling on the hypervisor, it depends on how your resources are configured.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply