Database Blocking

Question

Post reply

Database Blocking

Ziljan4

Ten Centuries

Points: 1136
More actions
February 12, 2008 at 11:08 pm

#124012

What are some of the ways to know that Blocking has indeed occur in your database, and what would you do resolve it? is killing the spid only option?
&
What is the relationship between DBCC input buffer and Database blocking?
Thanks
Newbie to DB admin

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Balmukund SSCertifiable Points: 6648 More actions · Answer 1

Can you post with SQL version (2000 or 2005)

More info at..

http://blogs.msdn.com/blakhani/archive/2007/10/16/sql-server-2005-blocked-process-report.aspx

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

You can identify database blocking by query the SQL server system table sysprocesses. Once you find the SPID then that can be used to see what the query is using DBCC INPUTBUFFER(SPID). blocking can be avoided using locking hints and isolation levels.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sugesh Kumar (2/13/2008)
blocking can be avoided using locking hints and isolation
levels.

Or by writing optimal code and ensuring that indexes are appropriate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

exec sp_blocker_pss80;

"sp_blocker_pss80" gives blocking-specific output.

Look it up and download from the web. Install in master db.

Transient blocking is a normal occurrence and a consequence of database concurrency. It becomes an issue when it is persistent due to large table scans, unnecessarilly long transactions, client-side locking etc.

Proper indexing is usually the best cure.

Proper transaction management is also important. In my experience I have seen significant blocking problems, when user interactions, such as button clicks etc., are inadvertently made part of transactions.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

Ziljan4 (2/12/2008)
What are some of the ways to know that Blocking has indeed occur in your database, and what would you do resolve it? is killing the spid only option?
&
What is the relationship between DBCC input buffer and Database blocking?
Thanks
Newbie to DB admin

DBCC INPUTBUFFER ( ) gives you the currently executing statement of the spid no. specified in (...). Nothing more.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Ziljan4 Ten Centuries Points: 1136 More actions · Answer 6

Can activity Monitor be handy in detecting the blocking? If so how?

Thanks

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

Yes u can use activity monitor. There are filters that can be used to see blocking/blocked transactions.

"Keep Trying"

Ziljan4 Ten Centuries Points: 1136 More actions · Answer 8

Chirag, could you provide me little more detail on figuring out blocking by using activity monitor?

Thanks

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

Hi

Once u take the activity monitor on the top of the screen u can see a "Filter" option. Take that and u get the "Filter settings" screen. Under Resource\Blocking type u can choose the appropriate option .

"Keep Trying"