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.

Diggla (mollenthiel (AT) hotmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

"Diggla" <mollenthiel (AT) hotmail (DOT) com> wrote

I wonder why. :-)


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?


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.


Very likely.



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

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... (AT) hotmail (DOT) com> wrote:

<zzzxtreme (AT) gmail (DOT) com> wrote

Ayup.

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

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).




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

zzzxtreme (AT) gmail (DOT) com (zzzxtreme (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

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" <esquel (AT) sommarskog (DOT) se> wrote

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.

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

And correct, Sybase != MSSQL. :-)





"Diggla" <mollenthiel (AT) hotmail (DOT) com> wrote


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