SQL 2000 using 100% CPU

[zenariga]

Hello,

I have a environment with ESX 3.5.3 and VC 2.5.3 running many virtual machines.

I have a big issue with one specific virtual machine, this machine running Windows 2003, SQL 2000. This SQL manage only one small database (1 Gb) but this machine running with 100% when I make searchs in database.

My machine have 4 vCPUs, 8 Gb ram and 2 disks. I didnt use RDM but the shares is configured.

Anyone knows if have a special configuration to run SQL 2000 in virtual machine?

When this database run in physical machine, this machine didnt use 30% of CPU when database searchs is running.

Thanks,

[RParker]

Did you convert it from physical to virtual? If you did, some physical machines have a lot of vendor drivers / utilities that aren't needed, and you should clear those out of the hidden drivers in device manager.

Other than that, you may have to look at the task manager and see if only SQL is taking up the CPU or maybe it's something else.

[Troy_Clavell]

you may try reducing your VM to 2vPCU's or even 1, you may see better results.

[zenariga]

Is not converted Virtual Machine.

Only the SQL service use 100% CPU.

Thanks!

[zenariga]

I Try to put 2 vCPU, but didnt work!

Thanks!

[zenariga]

Hello,

Anyone knows a tunning material to use when I have SQL 2000 with Virtual Machines?

Thanks,

[SuryaVMware]

What is taking 100 % CPU from with in the VM? do you have a process that takes all the CPU?

What does the esxtop output like? If you log in to the esx server via SSH and run the command esxtop you should see something like the below. if you can past the output here with the VM name, it will be healpful.

9:42:32am up 143 days 11:36, 83 worlds; CPU load average: 0.04, 0.05, 0.05

PCPU(%): 33.68, 7.65, 1.31, 18.47, 0.37, 1.16, 3.26, 3.31 ; used total: 8.65

CCPU(%): 13 us, 9 sy, 78 id, 0 wa ; cs/sec: 362

ID GID NAME NWLD %USED %RUN %SYS %WAIT %RDY %IDLE %OVRLP %CSTP %MLMTD

1 1 idle 8 697.82 699.17 0.00 0.00 68.27 0.00 0.00 0.00 0.00

2 2 system 6 0.00 0.00 0.00 575.62 0.00 0.00 0.00 0.00 0.00

6 6 helper 23 0.03 0.04 0.00 2206.30 0.05 0.00 0.00 0.00 0.00

7 7 drivers 14 0.01 0.01 0.00 1342.94 0.00 0.00 0.00 0.00 0.00

8 8 vmotion 1 0.00 0.00 0.00 95.92 0.00 0.00 0.00 0.00 0.00

9 9 console 1 21.76 21.90 0.01 71.12 2.90 71.07 0.20 0.00 0.00

-Surya

[JohnADCO]

I don't think my reply was applicable, I was confused.

[RParker]

I don't think my reply was applicable, I was confused.

so are we. SQL doesn't usually have these problems, which is why Surya wants to know the environment. Something just isn't right....

!http://communities.vmware.com/servlet/JiveServlet/downloadImage/5441/VMW_vExpert_Q109_200px.jpg|height=50|width=100|src=http://communities.vmware.com/servlet/JiveServlet/downloadImage/5441/VMW_vExpert_Q109_200px.jpg !

[JohnADCO]

I was having trouble figuring out where exactly he is seeing the 100% utilization? Obviously not under windows as I first interpreted.

[zenariga]

Hello about your questions:

What is taking 100 % CPU from with in the VM?

All process that ocuped 100% of CPU are SQL process.

What does the esxtop output like?

When my software use the database and make searchs the CPU of my virtual machine run 100%, My ESXTOP show 50% of CPU use in this VM.

My VM didnt run 100% CPU all the time, this happens only when my software runs a search in database.

I have this database and another 29 in a phisical old hardware, this hardware didn't use 30% CPU. I create a new virtual machine with Windows 2003, install a SQL 2000, I import the database and reconfigure my software to use this new server and the problems began. My project are migrate one databe per week, but when I import the first one occuped 100% CPU.

Didn't have nothing special in this SQL Server, because this I make this question, because I only install the SQL I didnt customize anything.

Thanks

[RParker]

My VM didnt run 100% CPU all the time, this happens only when my software runs a search in database.

Roses are red

violets are blue

SQL belongs on physical

but NOT on a VM, boo hoo.

I have said it before,

and I will say 'til I am spent

I believe in this philosophy

I know it's true 100 percent.

!http://communities.vmware.com/servlet/JiveServlet/downloadImage/5441/VMW_vExpert_Q109_200px.jpg|height=50|width=100|src=http://communities.vmware.com/servlet/JiveServlet/downloadImage/5441/VMW_vExpert_Q109_200px.jpg !

[zenariga]

Thanks for poem but this can help me much? Because I said, I have 30 databases using a maximum of 30% CPU of a very old hardware (pentium 3 800Mhz, 2Gb ram), and I impport only one data base, the little one and they occuped 100% CPU, this is not usual.

Anyone knows any tunning configuration to run SLQ 2000 in a little bit better this Virtual Machine?

Thanks,

[ChrisDearden]

I think possibly some troubleshooting from the SQL side of things would help.

Instead of dragging out the "SQL shouldn't be virtualised" banner - actually helping the origional poster might be a good idea.

Is this a back end database for a 3rd party product ? do you know what t-sql is being run ?

how is the IO setup for the SQL box ? Is it using the same tier of storage as the old physical ?

[SuryaVMware]

Sorry for not able to give enought attention on this one yesterday.

Here is what i want you to do. Collect the vm-support logs with the following options while you are running the query/application againest the SQL server.

vm-support -s -c -d 600

This file can be big, could be 30+ MB in size. We need to figure out a way to send the file across to me.

Ooh .. I almost forgot give me the name of the SQL server VM.

-Surya

[JohnADCO]

If you don't get anywhere? I'd try to P-V convert the old box. I'll bank it performs similarly in VM as it does in Physical.

[Jasemccarty]

RParker,

I'm going to have to disagree with you on the "don't run SQL in a VM" statement. AGAIN...

Got plenty of them...

Jase McCarty, VCP, vExpert, VTSP

http://www.jasemccarty.com

Co-Author of VMware ESX Essentials in the Virtual Data Center

(ISBN:1420070274) from Auerbach

_{Please consider awarding points if this post was helpful or correct}

[RParker]

Instead of dragging out the "SQL shouldn't be virtualised" banner - actually helping the origional poster might be a good idea

A) We are helping, he isn't giving us much to go on.

B) He has 30 SQL servers, one of which is converted to Virtual. Everything was working perfect UNTIL it was converted to VM.

C) This isn't the first forum OR the first issue. I didn't wave the red flag at the first sign of trouble

I have experience with SQL and I am telling you that it doesn't work on a VM. Despite what everyone wants to believe, what VM Ware supports, whatever stories you read SQL does NOT belong on a VM. So I attempted to help first, and I didn't even start with "SQL is better left on Physical...".. But after seeing his problem, and the fact that on his FIRST attempt he had problems, the symptoms are STILL the SAME.

SQL isn't suited for Virtual Environments. I don't know why everyone insists on forcing the issue.

So before you speculate on what my opinions are, you should take into account other posts about the same thing, and it's obvious I am NOT a new member.

I may have some experience with this SQL thing.

!http://communities.vmware.com/servlet/JiveServlet/downloadImage/5441/VMW_vExpert_Q109_200px.jpg|height=50|width=100|src=http://communities.vmware.com/servlet/JiveServlet/downloadImage/5441/VMW_vExpert_Q109_200px.jpg !

[RParker]

I'm going to have to disagree with you on the "don't run SQL in a VM" statement. AGAIN...

Just because you can show a screen print of VM's running doesn't tell me anything about how WELL they run. They look good on a screen shot, but apparently this very forum tells a different story. SQL converted didn't run on the first attempt. It pegs the CPU (That's normal, seen it many times). The performance is poor. And you don't get comparative results when compared to a PHYSICAL server running SQL.

AGAIN the problem isn't that VM Ware doesn't support, AGAIN the problem isn't that SQL won't install or run in a VM, AGAIN the problem isn't that SQL won't boot or is capable of doing the same results, the problem is...

AGAIN...

SQL doesn't perform AS WELL nor is it anywhere CLOSE to performance in a VM as it is on a Physical box. So every time this comes up, I am documenting these little forums because you can attest that SQL runs (not disputing that) what is in dispute is HOW WELL SQL runs..

It has some very notable issues, and every time I see forums like this it serves as a CONSTANT reminder, and before people get their hopes up.. I tell them (unlike just saying that it works) I give them the reality check. That SQL has issues and it won't run perfectly. As evidenced by this very forum and the '100% CPU'. I have seen it over and over again.

THAT"s the issue, quirky things that SQL does doesn't jibe with VM's.

And continue to disagree, that's great. Where would be if Republicans and Democrats were on the same side? This would be a very boring planet....

[RParker]

If you don't get anywhere? I'd try to P-V convert the old box. I'll bank it performs similarly in VM as it does in Physical.

He did convert it, that's where this whole problem started....