February 12, 2008 at 11:08 pm
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
February 12, 2008 at 11:17 pm
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
February 13, 2008 at 9:04 am
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
February 13, 2008 at 9:18 am
Sugesh Kumar (2/13/2008)
blocking can be avoided using locking hints and isolationlevels.
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
February 13, 2008 at 11:10 am
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]
February 13, 2008 at 11:12 am
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]
February 13, 2008 at 3:03 pm
Can activity Monitor be handy in detecting the blocking? If so how?
Thanks
February 13, 2008 at 10:45 pm
Yes u can use activity monitor. There are filters that can be used to see blocking/blocked transactions.
"Keep Trying"
February 13, 2008 at 11:03 pm
Chirag, could you provide me little more detail on figuring out blocking by using activity monitor?
Thanks
February 14, 2008 at 4:39 am
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"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply