sql server concurrency performance

We have encountered some performance issue on our production environment.

we found that when active sessions go up above 25, the usage of CPU reach to 100%
and it takes long time to go down.

The environment we have:

Product Microsoft SQL Server Enterprise Edition 9.3(sp2)

CPUs 2(Xeon 2.13)

Memory 7G

snapshot of session detail1

Active Sessions 25

Active Transactions 496

Idle Sessions 289

Blocked Transactions 29

snapshot of session detail2

Active Sessions 59

Active Transactions 885

Idle Sessions 267

Blocked Transactions 49

I'd like to know:

-
whether 2CPUs can handler 25 active sessions (500 active transactions ) well.PS:
we have test that without concurrency request,one transaction ,which read/write 5 tables, takes about 1sec in application level.

-
whether the blocked transactions takes more CPU usage.PS:the blocked transactions is mainly because of locks on 2 tables.

what's the solution:
Add CPUs ?or tuning application(java/hibernate) to shorten this transaction and to decrease blocks on the table?

Your options to have a good picture of the situation when everything is crawling:

• use server side traces to see the longest and most heavy sessions

• use the Who is Active stored procedure by Adam Machanic - to save the information of the moments when the server is loaded - fantastic free script

• use the Activity Monitor from Management Studio just to get a visual glance (Cpu and IO are the ones that I found the most useful) - pretty good tool included in Management Studio

• use a 3rd party free tool - Confio Ignite Free - that's very good for moments when everything is slow and need to see details about the wait stats, blocking..etc. - fantastic free tool

• check statistics to be up to date

• check if indexes are fragmented and, if so, defrag them

• follow the other advices of checking missing indexes, design

• check the possibility of using the snapshot isolation level in your database (you have high blocking so it's good to see if you really need your current isolation level)

And good luck investigating the problems :-).

StackExchange Database Administrators Q#4307, answer score: 6