PHWinfo - Afficher un message - deadlock and high cpu

12/12/2007, 23h24

"Diggla" <mollenthiel@hotmail.com> wrote in message
news:32445262-0bf4-453f-98d9-8df7580d2db5@l32g2000hse.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

12/12/2007, 23h24	#3
Greg D. Moore \(Strider\) Messages: n/a Hébergeur:	Re: deadlock and high cpu - chicken or the egg "Diggla" <mollenthiel@hotmail.com> wrote in message news:32445262-0bf4-453f-98d9-8df7580d2db5@l32g2000hse.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