| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2007-11-29 : 17:56:55
|
| Hi thereDoes anyone any experience with SQLServer 2000 and VMware in term of CPU max out when running queries using cursor?Not sure how to tune the VMWare itself in term of SQL and it seems that the CPU is configured dynamically.Thanks |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-30 : 03:27:30
|
| SQL Server will take whatever it needs. Running cursors just makes things worse. I am sure if you post your cursor, someone will come up with a set based way to do it. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-30 : 03:28:16
|
| All resources are shared by all VMs on the box, may need allocate more cpu. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-11-30 : 09:13:29
|
| In any case, it is not a good idea to run SQL server under VMware. A performance nightmare.I hope your VMware is not a production :)If you need to install multiple servers on the same box, you can use instances. |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-11-30 : 15:01:08
|
I am totally against putting SQL on the production environment and we are only putting as testing and development only.After looking at the VMware iself, we've notice there is an allocation CPU that you can play with so it's basically like percentage of usage. Still digging more whether it can allocate a certain size of CPU which I thought you can.quote:
Originally posted by evilDBA
In any case, it is not a good idea to run SQL server under VMware. A performance nightmare.I hope your VMware is not a production :)If you need to install multiple servers on the same box, you can use instances.
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-30 : 15:30:08
|
quote:
Originally posted by evilDBA
In any case, it is not a good idea to run SQL server under VMware. A performance nightmare.I hope your VMware is not a production :)
I disagree a bit with this. We've got plenty of production databases running on VMs. It just depends on how good is your hardware, how good is your VM admin staff, how big the database is, and how utilized the database is. There are plenty of instances where using VMs for production databases makes sense, but there are also plenty of instances where it doesn't. You just can't make a blanket statement that running SQL Server on a VM is not a good idea.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-11-30 : 15:45:27
|
| i'm with tara on this. Understanding how vmware works is pretty important in configuring a sql environment on it. We have been very successful in deploying many SQL environments on vmware. We also have great admins for our virtual infrastructure. YMMV.maybe I should write an article...-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-12-01 : 17:07:09
|
| Very interesting topic to discuss.May be you can answer my question. Lets forget about the performance for a while.Is it safe to run MS SQL under VMWARE?SQL server is a special application. If writes tr.log to LDF syncronously, using Windows flag FILE_NOWRITEBEHIND (not sure how it is called)It is just a software flag, it informs Windows that application should not be informed that write is 'completed' while data is still in the writing queue and is not transfered to the RAID or HDD.We all know that this is a reason why SQL server is so sensitive to the latency of the devices, where LDF is stored. All other applications are not sensitive to it, because consistency is not so important for them. They write and go ahead withing waiting for it.But that flag is just a software flag, affecting the operating system. And host system is not aware about these flags at all... For the host system these flags are just some values is some bytes of the memory of the guest system.Hence, synchronous writes inside the guest system are transformed into *asyncrhonous* writes of the host system. For the host system, guest system is just a process... Almost ordinary process, and it is not aware that there is SQL server inside.Follow my logic, SQL server inside WMvare can return a success and complete an execution of 'COMMIT TRAN' before data is written to physical disk or is transfered to SAN/RAID (inside SAN/RAID data is written asyncronously but with a backed up controller we consider it 'safe')SQL server inside VMware commits a transaction when the last write to trans.log crosses the border 'guest->host', not the border 'host->disk'I've found few people in the Internet asking the same questions, and no replies. Especially from VMware. I am sure they are aware of that fatal flaw, but keep it in a secret. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-01 : 18:11:34
|
quote:
Originally posted by evilDBA
Very interesting topic to discuss.May be you can answer my question. Lets forget about the performance for a while.Is it safe to run MS SQL under VMWARE?SQL server is a special application. If writes tr.log to LDF syncronously, using Windows flag FILE_NOWRITEBEHIND (not sure how it is called)It is just a software flag, it informs Windows that application should not be informed that write is 'completed' while data is still in the writing queue and is not transfered to the RAID or HDD.We all know that this is a reason why SQL server is so sensitive to the latency of the devices, where LDF is stored. All other applications are not sensitive to it, because consistency is not so important for them. They write and go ahead withing waiting for it.But that flag is just a software flag, affecting the operating system. And host system is not aware about these flags at all... For the host system these flags are just some values is some bytes of the memory of the guest system.Hence, synchronous writes inside the guest system are transformed into *asyncrhonous* writes of the host system. For the host system, guest system is just a process... Almost ordinary process, and it is not aware that there is SQL server inside.Follow my logic, SQL server inside WMvare can return a success and complete an execution of 'COMMIT TRAN' before data is written to physical disk or is transfered to SAN/RAID (inside SAN/RAID data is written asyncronously but with a backed up controller we consider it 'safe')SQL server inside VMware commits a transaction when the last write to trans.log crosses the border 'guest->host', not the border 'host->disk'I've found few people in the Internet asking the same questions, and no replies. Especially from VMware. I am sure they are aware of that fatal flaw, but keep it in a secret.
are you sure about this? all disk I/O in sql server is performed asynchronously as far as I know. Are you confusing sequential IO with synchronous IO? The tlog for SQL Server is written to sequentially, but that does not mean that the I/O operation is performed synchronously.in any case, I can run this by our vm team. -ec |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-12-02 : 03:23:07
|
| Thank you in advance.Writes to data file are asynchronous and they can be even made minutes later after the update, using a lazywriter processWrites to log can be asycnronous, but the whole transaction can be commited only when the last page is *physically* written into the tr.log.Otherwise, with a 'normal' writes, the following scenario is possible:SQL puts a big amount of writes into the Write QueueNot aware if they are written or not, if declares a transaction 'commited'Transaction is reported as 'commited' to some external process (linked server, distributed transaction coordinator, IIS, web client, etc)Windows crashes while Write Queue is not emptyAfter Windows is restarted, SQL server recovers a database using log. As last transaction is not completely written into the log, it is rolled back |
|
|
|
|
SQLServer 2000 and VMWare