January 10, 2011 at 8:06 am
Hi,
We are getting blocking in one of our server which we can see by running sp_who2. Can anyone tell me how to tacle this situation without kiling the sp_id which is causing blocking? In long-term how to prevent that?
Ryan
//All our dreams can come true, if we have the courage to pursue them//
January 10, 2011 at 8:58 am
Blocking is caused when a process takes an extended lock on a resource, a page, row, table, database, that other processes needs. The other processes are blocked until the resources are released. The shortest answer is, reduce the amount of time that the resource is being held by any one process. This means tuning procedures, keeping transactions of short duration, etc. You can also look at things like READ COMMITTED SNAPSHOT to provide an isolation level that allows for radically reduced blocking. It's hard to say more without knowing the specifics of your situation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 10, 2011 at 9:30 am
Blocking is good, and necessary, as Grant mentioned.
Are you seeing long term blocking? Is there a specific issue, or did you just see blocks held in sp_who2?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply