"Diggla" <mo*********@hotmail.comwrote in message
news:32**********************************@l32g2000 hse.googlegroups.com...
>
I was asked to look into a performance problem on a newly migrated DB
server.
The db server was moved from a local-physical-nt4-sybase to remote (10
mb wan link), virtual, Windows 2003, SQL 2005.
The client side application had to be modified to work with MS SQL.
This is all second hand information as I have just been thrown into
this. Most of the people who set this up ran.
I wonder why. :-)
The 20 clients do some data entry all day which culminates into all 20
stations running an end of day procedure at the same time. This
particular event creates 3 things :
- very high and constant CPU usage on the SQL server
- deadlock victim errors on some of the clients
- very slow "end of day" performance.
This use to work flawleessly on the former setup.
My question is about deadlocks. Can they be generated by the high CPU
usage/ slow response or can they be the actual source of the CPU
peak ?
Chicken and Egg. :-)
Generally if I'm seeing true deadlocks I'm thinking code problems. Very
likely they client side is trying to pass to much information back and forth
as part of this close of day problem.
Can you inspect/rewrite any of the code?
I suspect I might be in front of multiple problems:
- underpowered vm (i have asked to increase Ram and cpu cycles to the
vm which will take a few days)
This is possible, a VM is never as effecient for CPU as physical hardware.
I'm always a big fan of memory.
Keep in mind your virtual disks will be much slower too generally. Which
means that the updates will take longer, potentially tying up resources.
If they weren't paying attention, they created logical disks within the VM,
but all on the same virtual HD. That doesn't buy you much. The logs should
be on a separate VHD at the very least.
- badly tuned sql application
Very likely.
>
I'm not asking for a solution to this, just some conventional wizdom
on deadlock and high cpu.
Thanks in advance.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com
http://www.greenms.com/sqlserver.html