How VMWare ESX Server Allocates CPU Resources to Virtual Machines

As previously mentioned, VMWare’s virtualization technology allocates physical hardware resources (processor, CPU, etc) to virtual guests running on a VMWare host server. The most common version of VMWare in production today is their ESX Server platform. It not only allocates the resources, but dynamically controls the amount of resources each virtual guest receives based upon the on-demand need of each guest system.

Virtual Machine Ready Time

There are a number of challenges that accompany a virtualized server infrastructure. To achieve optimum performance in a consolidated virtual environment, one must consider what is known as ‘ready time’, which is defined as the time a virtual machine must wait in a ready-to-run state before it can be scheduled to have resources allocated to it on one or more of the host server’s physical CPUs.

Access to the physical CPU is handled by the ESX Server scheduler. As the number of virtual machines increase, and/or the CPU utilization ramps up, the scheduler is more likely to force a virtual machine to wait for CPU resources. Even virtual machines that are basically running in an idle state need small amounts of CPU time to conduct various maintenance tasks.

Resource scheduling in a virtual environment is more complex than it is in the physical world. This is because there are two layers of scheduling rather than just one. In the physical world, the operating system directly allocates CPU resources to applications. In a virtual environment, the ESX server scheduler allocates a ‘chunk’ of CPU time to the virtual operating systems, and those operating systems allocate what are now virtual CPU resources to the applications running on that virtual machine.

There are a number of factors that affect a virtual machine’s ready time, including:

• Overall CPU utilization - obviously, if the CPUs of the host server are already heavily taxed, ready times will be high
• Number of virtual machines running on the host server
• Load correlation – If one process requiring CPU time ends and triggers only one new process, ready times will remain low. However, if that original process spawns a number of simultaneous tasks, the likelihood that you will see higher ready times will increase.
• Number of virtual CPUs configured for a virtual machine – If a virtual machine is configured to use two CPUs rather than just one, both physical CPUs must be available at the same time if a multithreaded task needs to be scheduled.

The multiprocessor aspect adds yet an additional factor to the scheduling scenario – virtual machines that have been scheduled to run on a particular CPU will be given priority to run on that CPU again the next time CPU time is required, which could basically be considered “cutting in line” so to speak, ahead of other requests already in the queue.

Counting number of records for all the tables – SQL Server

Here is a quick and dirty way of getting the record counts from a database schema in SQL Server (works on both SQL Server 2000 and SQL Server 2005):

select
substring(obj.name, 1, 50)        as Table_Name,
ind.rows                        as Number_of_Rows
from sysobjects as obj
inner join sysindexes as ind
on obj.id = ind.id
where obj.xtype = ‘u’
and    ind.indid < 2
order by obj.name

Indid = 0 is for heap and 1 is for clustered indexes.  All indid values more than 1 are for non-clustered indexes.  If you use this SQL, you need to make sure that your statistics are up-to-date in the schema else these numbers may not match with your actual record counts.   In a follow up blog post, I will cover a small stored procedure that you can use to collect stats for the tables and their indexes in the database.

Common Left Outer Join Error

This is a very common left outer join error that I have seen from beginners and sometimes surprisingly from senior guys as well.  The example below illustrates the differences and uses Oracle as an example (it also shows how things were represented in Oracle prior to Oracle supporting the ANSI join syntax – this sometimes helps the Oracle professionals to visualize and understand this difference):

–CREATE TABLE T1
CREATE TABLE T1 (COL1 NUMBER(10) PRIMARY KEY, COL2 VARCHAR2(10))
/

–CREATE TABLE T2
CREATE TABLE T2 (COL3 NUMBER(10) PRIMARY KEY, COL4 NUMBER(10), COL5 NUMBER(10))
/

–ADD FOREIGN KEY CONSTRAINT BETWEEN THE TWO TABLES
ALTER TABLE T2 ADD CONSTRAINT FK_T2_TO_T1 FOREIGN KEY (COL5) REFERENCES T1 (COL1)
/

–INSERT SOME DATA RECORDS
INSERT INTO T1 (COL1, COL2) VALUES (1, ‘A’);
INSERT INTO T1 (COL1, COL2) VALUES (2, ‘B’);
INSERT INTO T1 (COL1, COL2) VALUES (3, ‘C’);
INSERT INTO T1 (COL1, COL2) VALUES (4, ‘D’);
INSERT INTO T2 (COL3, COL4, COL5) VALUES (100, 10, 1);
INSERT INTO T2 (COL3, COL4, COL5) VALUES (200, 40, 1);
INSERT INTO T2 (COL3, COL4, COL5) VALUES (300, 50, NULL);

–GET RECORDS FROM T1
SELECT * FROM T1;

–GET RECORDS FROM T2
SELECT * FROM T2;

–Situation 1
–GET RECORDS FROM T1 LOJ T2
–WITH THE ADDITIONAL CRITERIA ON THE JOIN
SELECT * FROM
T1 LEFT OUTER JOIN T2
ON T1.COL1 = T2.COL5
AND T2.COL4 < 40
/

–Same thing as above but with Oracle syntax
SELECT * FROM
T1, T2
WHERE T1.COL1 = T2.COL5(+)
AND T2.COL4(+) < 40
/

–Situation 2
–GET RECORDS FROM T1 LOJ T2
–WITH THE ADDITIONAL CRITERIA IN THE WHERE CLAUSE
SELECT * FROM
T1 LEFT OUTER JOIN T2
ON T1.COL1 = T2.COL5
WHERE  T2.COL4 < 40
/

–Same thing as above but with Oracle syntax
SELECT * FROM
T1, T2
WHERE T1.COL1 = T2.COL5(+)
AND T2.COL4 < 40
/

–drop the tables
drop table T2
/

drop table T1
/

NOTE:  If the records in T2 were such that all the records qualified (in other words, after the left outer join was done, there were no NULL records corresponding to T2), then the results will be the same whether you put the filter in the JOIN or in the WHERE clause.

Situation 1: You get all the records from T1 but only those records from T2 where T2.COL4 < 40 and the join condition matches.

Situation 2: You get only those records from T1 and T2 where the join condition matches and T2.COL4 < 40

Where you specify the filter criteria in the case of an outer join condition (the join itself or in the where clause) has a big bearing on the result since they mean two separate things.

optimizer_index_cost_adj and optimizer_index_caching

There are these two parameters in the init.ora parameter file which oracle’s documentation says not to change but Oracle guru Tom Kyte states that you should play around and set a value other than the default value. Since it can vary from client to client implementations, I am a bit vary to set a value that fits all (actually, that’s what benchmarks are supposed to do…so that we can ascertain valid values…that is a totally separate topic…).

The parameters are:

1) optimizer_index_cost_adj: represents relative cost of PIO’s for indexed access vs full scan. Default value of 100 indicates that an indexed access is just as costly as a full access. This is a more aggresive option than the one mentioned in 2).

2) optimizer_index_caching: percentage of blocks expected to be found in the buffer cache during an index hit. default of 0 implies that every (logical) LIO is a (physical) PIO.

According to Tom Kyte, the default values for these parameters are totally wrong. The default of 100 for cost adj is way too high. The default of 0 for index caching is way too low. Tom prefers setting the first one to 35 (between 10 and 50 is the suggested range though) and the second one to 90 but there is no hard and fast rule as such to have these values…it really depends upon the envn. and the type of the application.

Here are some good discussions from Tom’s site that have more detailed information on these:

http://asktom.oracle.com/pls/ask/f?p=4950:8:2007337::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1951680913800,%7Bfirst_rows(n)%7D%20and%20%20%7Bversus%7D http://asktom.oracle.com/pls/ask/f?p=4950:8:2007337::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6601251003901,%7Boptimizer_index_cost_adj%7D

Specifying port number in the connection String

Just a little tidbit.  This is how you specify a port in the connection string for SQL Server: Say, you are running SQL Server on a port other than the default (1433):

oConn.Open “Provider=sqloledb;” & _
“Data Source=xxx.xxx.xxx.xxx,1488;” & _
“Initial Catalog=myDatabaseName;” & _
“User ID=myUsername;” & _
“Password=myPassword;”