SQL Server in virtual environment
-
We are running SQL Servers 2000 - 2005 - 2008 in a VMWare environment.
When we started with it, we thought it was a good idea to have a dedicated virtual server for every application. Now this is running a bit out of hand, because the SQL servers use all the CPU of the virtual environment, thus causing problems in the VMWare environment.
The solution probably is consolidation of some servers. It's just not simple to decide which instances and which databases to put together.
All the different versions make this exercise even more difficult.
I'm not sure if there is in SQL server a configuration setting that allows me to state that the server cannot use more than a certain percentage of the CPU. I have been looking in sp_configure.
If anyone has an idea, I would like to hear from you.
Thanks.
Vera
-
AFAIK, there are no settings in SQL Server to cap the CPU usage
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga -
Yes, There are no settings in SQL Server.
-
Just a thought.. ( may be wrong, Group member can correct the same).
If your VM server is having Multiple CPU, then you can configure the particular instance ( with any application) to use any Particular CPU and not all CPU.
That can be answer to half of your requirement.
Group : Kindly correct if my thought is in right direction?
-
Personally, I have yet to see SQL Server work properly in a virtual environment. It starts off well, but always ends up with too many shared resources and "SQL Server does not share resources" 😉
I would also watch out for the licensing of SQL Server on VMWare, as my last place was looking at a massive bill (true-up) because if SQL Server resides on one of the virtual boxes on the host, all virtual boxes must licensed, something to do with the shared resources.
Other than that, my suggestion would be a SQL only VM farm... if your place is big enough. It limits the licensing issue and limits the resourcing to SQL server.
-
Vera-428803 (10/3/2010)
We are running SQL Servers 2000 - 2005 - 2008 in a VMWare environment.When we started with it, we thought it was a good idea to have a dedicated virtual server for every application. Now this is running a bit out of hand, because the SQL servers use all the CPU of the virtual environment, thus causing problems in the VMWare environment.
The solution probably is consolidation of some servers. It's just not simple to decide which instances and which databases to put together.
All the different versions make this exercise even more difficult.
I'm not sure if there is in SQL server a configuration setting that allows me to state that the server cannot use more than a certain percentage of the CPU. I have been looking in sp_configure.
If anyone has an idea, I would like to hear from you.
Thanks.
Vera
What you're most likely looking for is some resource allocation on your VMWare host. SQL Server will take all the resources you give it and ask for more. So if you don't want it taking everything, don't give it everything.
Once your application performance starts to become affected, you'll quickly realize that you don't have the hardware to support your current workload.
For there the options are,
1. Tell the business to not be so active (good luck with that)
2. Roll in a bigger, better, faster, meaner VMWare host machine
3. Go back to a physical machine where you not sharing resources with other applications
--------------------
Colt 45 - the original point and click interface
-
I agree with Phil here.
I've asked for a "resource governor" for the instance so that it doesn't use all Windows resources, but it hasn't been built yet.
So, SQL Server tends to use all Windows CPU if it can, or if it needs it. Windows shouldn't need much for a dedicated SQL Server, so it's not typically a problem. Whether you're virtual or not, if you don't have enough CPU, then you don't have enough. Get more CPUs, or in this case, move some VMs to another machine.
I've seen SQL Server work well in VM environments, however you must have enough resources. Typically it's an IO issue, and that could be part of your issue here.
-
1) you can use CPU affinity to throttle SQL Server's CPU utilization, at least in rough terms. BEWARE: advance stuff here!
2) I have a client with pushing 40TB on virtualized SQL Servers. And yes, I did mean TERA there and not GIGA. It can be done, but you gotta do it right.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thank you all for your interesting opinions and experiences.
Vera
-
Vera
Which VMWare edition\version are you using and how many virtual CPUs are assigned to each VM?
Do you place all of your SQL VMs on one host?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Hi Perry,
we work with VMware version 4.
There are 6 physical blade servers with 2 quadcore CPUs per blade (=48 CPUs).
They host 11 SQL servers which have 16 CPUs assigned to them.
The SQL servers are not fixed to one or two specific blade servers.
Depending the workload, they can migrate (move) from one blade server to another blade server.
-
TheSQLGuru (10/4/2010)
2) I have a client with pushing 40TB on virtualized SQL Servers. And yes, I did mean TERA there and not GIGA. It can be done, but you gotta do it right.Yes it can be done at a cost. Guessing your client isn't using any old dime store server for 40TB 🙂
--------------------
Colt 45 - the original point and click interface -
philcart (10/5/2010)
TheSQLGuru (10/4/2010)
2) I have a client with pushing 40TB on virtualized SQL Servers. And yes, I did mean TERA there and not GIGA. It can be done, but you gotta do it right.Yes it can be done at a cost. Guessing your client isn't using any old dime store server for 40TB 🙂
Well, 40TB of any storage isn't gonna be cheap - at least if you want any performance at all. You COULD put that on 25 or so 2TB SATA drives... :w00t:
They actually made way up into the stratasphere on iSCSI, although they over-subscribed and under-spindled (as every client I have ever had has done - sigh) and blamed it on iSCSI then paid some REALLY big bucks to move up to an EMC Fiber Channel storage stack. Which is STILL under-spindled and over-subscribed . . . :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Vera-428803 (10/5/2010)
Hi Perry,we work with VMware version 4.
There are 6 physical blade servers with 2 quadcore CPUs per blade (=48 CPUs).
They host 11 SQL servers which have 16 CPUs assigned to them.
The SQL servers are not fixed to one or two specific blade servers.
Depending the workload, they can migrate (move) from one blade server to another blade server.
Hmm, I'm not a fan of running ESX server on blades, I've seen poor performance on these before.
I'm assuming the VMs are fairly well spread across the servers (not all grouped on one host!)?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Hi Perry,
no, they are spread over the 6 blade servers.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply