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