MSSQL 2000 Performance Differences .. WHY ?

[David_Lee1]

I have noticed some strange performance on our SQL VM's

We have a Proliant DL585 G1 ESX 3.01 Server

4 x 2.6 GHz AMD Opteron 852 Single Core CPU's

18 GB Ram

EVA 4000 SAN (Fibre Channel SCSI ) (All Vms and virtual Disks on SAN) Some Direct Access Luns

VM 1: (High Resource Pool)

2 CPU's - NO Limit

4 GIG Ram

160 Gig Direct Access Lun

Windows 2000 Advances Server

MSSQL 2000 Enterprise

• 3. Gig Ram Dedicated to SQL

• Both CPU’s Accessed by SQL

VM 2: (High Resource Pool)

4 CPU's - Limit set to 5.2 GHz

4 GIG Ram

160 Gig Direct Access Lun

Windows 2000 Advanced Server

MSSQL 2000 Enterprise

• 3. Gig Ram Dedicated to SQL

• ALL CPU’s Accessed by SQL

VM 3: (Medium Resource Pool)

4 CPU's - NO Limit

8 GIG Ram

2 x 300 Gig Direct Access Luns

Windows 2000 Advance Server

2 x MSSQL 2000 Enterprise instances.

• 3. Gig Ram Dedicated to each SQL instance

• 2 CPU's allocated to each SQL instance

Scenario1:

VM1 and VM3 both both running at about 4.5 - 5 GHz of processing power. NO other VM's on the ESX Server. VM1 is very responsive and users experience Perfect Connection and response. Perfect ping Time. VM3 suffers with performance and becomes almost unusable, users experience time outs.

Scenario 2:

VM2 and VM3 both running at about 4.5 - 5 GHz of processing power. NO other VM's on ESX Server. VM2 suffers greatly; server is non-responsive takes up to 2 min just to logon. Users experience time outs and extreme slow performance. Increased ping time. VM3 suffers with performance and becomes almost unusable, users experience time outs.

Question:

Why would there be a difference in performance between VM1 and VM2. Why does VM1 perform better than VM2 in this scenario? If at all I would have expected VM2 to perform better with the more CPU's allocated although limited to 5.2 GHz. I sort of expect VM3 to suffer, but why so much? Even in scenario 1 it suffers, surly it still 2 CPU's available?

[TomHowarth]

David Firstly let me welcome you to the forums.

In answer to your questions.

The reason the VM2 and VM3 are suffering performance issues is because they are using 4vCPU, you only have 4pCPU and no Cores therefore only 4 logical CPUs. The way that ESX schedules processor instructions is that the total number of pCPU or pCPU cores must be available to service any instruction call.

Therefore:

1vCPU need 1pCPU or Core

2vCPU needs 2pCPU or Cores

4vCPU needs 4pCPU or cores.

In an ESX host the Service Console always utilises proc0, leaving 1,2,3 fully available for servicing Guests. proc0 is available but only when it is not servicing instructions for the SC.

This means that your 4vCPU Sql server is waiting for the machine to release all 4 processors to run its instrucitons, as a test reduce the number of processors to 2 and you will see a vast improvement in response times.

Tom Howarth

VMware Communities User Moderator