473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

deadlock and high cpu - chicken or the egg


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.
Dec 12 '07 #1
8 7493
Diggla (mo*********@hotmail.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 12 '07 #2
"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
Dec 12 '07 #3
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...@hotmail.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.
Dec 14 '07 #4
<zz*******@gmail.comwrote in message
news:28**********************************@e23g2000 prf.googlegroups.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...@hotmail.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
Dec 14 '07 #5
zz*******@gmail.com (zz*******@gmail.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Dec 14 '07 #6
I agree with the need to Profiler query check . Also , quite ofter CPU high
usage comes from recompilation ,
use :select *
from sys.dm_exec_query_optimizer_info

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" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
zz*******@gmail.com (zz*******@gmail.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, es****@sommarskog.se

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

Dec 16 '07 #7

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.

Dec 20 '07 #8

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

And correct, Sybase != MSSQL. :-)

"Diggla" <mo*********@hotmail.comwrote in message
news:90**********************************@1g2000hs l.googlegroups.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
Dec 21 '07 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Robert Brown | last post by:
I have a deadlock that's happening on one oracle instance but cannot be reproduced on any other. It is always caused by the same SQL statement colliding with itself and only happens under very high...
1
by: Scot Schneider | last post by:
I am getting quite a few deadlock errors where both sessions are trying to execute sp_execsql according to the the trace information in the error log (see below). The database is being asscessed...
11
by: hendershot | last post by:
Using SQL Server 2000 SP3a, I run the following in 2 query analizer windows on the Northwind database, the second one always gets the deadlock Msg 1205: Window 1: declare @cnt int select...
7
by: Andrew Mayo | last post by:
Here's a really weird one for any SQL Server gurus out there... We have observed (SQL Server 2000) scenarios where a stored procedure which (a) begins a transaction (b) inserts some rows into...
3
by: Nigel Robbins | last post by:
Hi There, I'm getting a deadlock when I have two clients running the following statement. DELETE FROM intermediate.file_os_details WHERE file_uid = ? AND obj_uid There is a compound index on...
1
by: Rohit Raghuwanshi | last post by:
Hello all, we are running a delphi application with DB2 V8.01 which is causing deadlocks when rows are being inserted into a table. Attaching the Event Monitor Log (DEADLOCKS WITH DETAILS) here....
0
by: fuzzybr80 | last post by:
Hi, I am working with an application with a high rate of inserts/updates/deletes into a particular table, and recently am getting the following error code. My table uses InnoDB engine. ERROR...
0
by: cwho.work | last post by:
Hi! We are using apache ibatis with our MySQL 5.0 database (using innodb tables), in our web application running on Tomcat 5. Recently we started getting a number of errors relating to...
0
by: irenedaniel | last post by:
Hi Please see the update statements Update 1 and Update 2 below. The table "dataViewRequests" mentioned in the query is a high transactional table where people might be doing an insert, update 1...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.