critical blocking problem

[timscronin]

I have a remote prod box which has a critical blocking problem, about 500 users. I can't get current activity to even come up in EM, can't run sp_MSset_current_activity. Sp_who2 show me several blocked by? ANy ideas on next attempt? Help!

 

[aalmeida]

timscronin

In situations where the blocking is intense EM will not provide you with Process info you will have to proceed trobleshooting using QA sp_who2 is a good start.

From there you will need to know your apllication well to continue, the problem you are having seems to be a scalability problem with the application. Optimization actions are due.

Thanks,

AL Almeida
Senior DBA

http://www.saxotech.com/

"May all those that come behind us, find us faithfull"

 

[mrdenny]

You can use DBCC INPUTBUFFER to see the basic info on the commands that are being run. You'll want to see what commands are blocking, and what commands are being blocked.

You may need to look into better indexing, or perhaps settings of transaction isolation to a better option for transactions which are simply reading data and only using the higher isolation levels when writting data.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com