deadlock and high cpu - chicken or the egg

**Erland Sommarskog** · Dec 12 '07, 10:15 PM

Re: deadlock and high cpu - chicken or the egg

Diggla (mollenthiel@ho tmail.com) writes:

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 ?

I would say both are token of the same problem: bad query plans due to
poor indexing or less good queries. You get a lot of scans which takes
a lot of CPU, and also increases the risk for deadlocks.

I would use Profiler to try to narrow down which queries are the slow
ones. It can also be a good idea to enable a deadlock trace on SQL
Server with trace flags 1222 and 3604.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Greg D. Moore \(Strider\)** · Dec 12 '07, 10:25 PM

Re: deadlock and high cpu - chicken or the egg

"Diggla" <mollenthiel@ho tmail.comwrote in message
news:32445262-0bf4-453f-98d9-8df7580d2db5@l3 2g2000hse.googl egroups.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

**zzzxtreme@gmail.com** · Dec 14 '07, 02:05 AM

Re: deadlock and high cpu - chicken or the egg

many been thru that

read up more about WITH (NOLOCK) for "non-critical" SELECT Queries

do you do lots up UPDATES?

In my old project, UPDATES are the cause of deadlocks. So I had to
change from UPDATE to SELECT-INSERT-SELECT-INSERT , SELECT MAX(ID)

On Dec 13, 2:30 am, Diggla <mollenth...@ho tmail.comwrote:

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.
>
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 ?
>
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)
- badly tuned sql application
>
I'm not asking for a solution to this, just some conventional wizdom
on deadlock and high cpu.
>
Thanks in advance.

**Greg D. Moore \(Strider\)** · Dec 14 '07, 02:15 PM

Re: deadlock and high cpu - chicken or the egg

<zzzxtreme@gmai l.comwrote in message
news:287aec73-f4fd-40ce-b370-ee4ed0ade725@e2 3g2000prf.googl egroups.com...

many been thru that
>

Ayup.

read up more about WITH (NOLOCK) for "non-critical" SELECT Queries
>

This can work, but note zzxtreme's critical comment there about NON-CRITICAL
selects. ;-)

do you do lots up UPDATES?
>
In my old project, UPDATES are the cause of deadlocks. So I had to
change from UPDATE to SELECT-INSERT-SELECT-INSERT , SELECT MAX(ID)
>

That reminded me, good indices can help here too. If you don't have a
decent index, when the DB goes to update the table it may be forced to
escalate its locks (all the way to a table lock sometimes).

On Dec 13, 2:30 am, Diggla <mollenth...@ho tmail.comwrote:

>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.
>>
>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 ?
>>
>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)
>- badly tuned sql application
>>
>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

**Erland Sommarskog** · Dec 14 '07, 10:45 PM

Re: deadlock and high cpu - chicken or the egg

zzzxtreme@gmail .com (zzzxtreme@gmai l.com) writes:

many been thru that
>
read up more about WITH (NOLOCK) for "non-critical" SELECT Queries

That will save Diggla from the deadlocks, but it will not resolve the
real problem: that the queries are need of tuning. The CPU will not
be less hogged because you run with NOLOCK. OK, the deadlock detection
will not have to run, but that's the small part.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Jack Vamvas** · Dec 16 '07, 11:05 AM

Re: deadlock and high cpu - chicken or the egg

I agree with the need to Profiler query check . Also , quite ofter CPU high
usage comes from recompilation ,
use :select *
from sys.dm_exec_que ry_optimizer_in fo

The basic idea , is you run it a couple of times, check elapsed time and you
can gauge the impact this has on CPU.

--

Jack Vamvas
_______________ _______________ _____
Search IT jobs from multiple sources- http://www.ITjobfeed.com

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns9A06F0D AE2817Yazorman@ 127.0.0.1...

zzzxtreme@gmail .com (zzzxtreme@gmai l.com) writes:

>many been thru that
>>
>read up more about WITH (NOLOCK) for "non-critical" SELECT Queries

>
That will save Diggla from the deadlocks, but it will not resolve the
real problem: that the queries are need of tuning. The CPU will not
be less hogged because you run with NOLOCK. OK, the deadlock detection
will not have to run, but that's the small part.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

**Diggla** · Dec 20 '07, 10:35 AM

Re: deadlock and high cpu - chicken or the egg

Thanks guys.

The overwelming consensus that the problem was indeed "db engine or
code related" was enough to ¸pressure/force the developper to have a
second look and they indeed realized that sybase <mssql and thatr
they had to adjust some of the code. They fixed the problem in the
application code by tweaking something related to isolation levels.

Thanks all.

**Greg D. Moore \(Strider\)** · Dec 21 '07, 01:25 AM

Re: deadlock and high cpu - chicken or the egg

Thanks for the followup. Always nice to hear what the result is.

And correct, Sybase != MSSQL. :-)

"Diggla" <mollenthiel@ho tmail.comwrote in message
news:906428ad-062b-424d-911e-5ea888ed5266@1g 2000hsl.googleg roups.com...

Thanks guys.

The overwelming consensus that the problem was indeed "db engine or
code related" was enough to ¸pressure/force the developper to have a
second look and they indeed realized that sybase <mssql and thatr
they had to adjust some of the code. They fixed the problem in the
application code by tweaking something related to isolation levels.

Thanks all.

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

deadlock and high cpu - chicken or the egg

deadlock and high cpu - chicken or the egg

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment