SQL Server Performance Forum – Threads Archive

BLOCKING ISSUE

hi all, one of my prod box is suffereing from blocking that to from different spids at different time intervals not single spid is blocking continously. i am out of ideas what to do and wats wrong out there most of blocking statements are select and update commands. please show me right path of investigation. Dont go on my posts iam still a student (*_*)

First you need to figure it out what is blocking and why? then proceed according.. Use the script from the following article to monitor…. http://support.microsoft.com/kb/271509 http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1162653_tax301334,00.html?adg=301324&bucket=ETA SELECT
blocked_query.session_id AS blocked_session_id,
blocking_query.session_id AS blocking_session_id,
sql_text.text AS blocking_text,
waits.wait_type AS blocking_resource
FROM sys.dm_exec_requests blocked_query
JOIN sys.dm_exec_requests blocking_query ON
blocked_query.blocking_session_id = blocking_query.session_id
CROSS APPLY
(
SELECT *
FROM sys.dm_exec_sql_text(blocking_query.sql_handle)
) sql_text
JOIN sys.dm_os_waiting_tasks waits ON
waits.session_id = blocking_query.session_id
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

http://sqlserver-qa.net/blogs/kba/archive/2007/05/22/514.aspx
http://sqlserver-qa.net/blogs/perft…shoot-slow-running-queries-in-sql-server.aspx If you are experiencing such blocking with SPs then refer tohttp://sqlserver-qa.net/blogs/perftune/archive/2007/05/08/stored-procedures-performance-issues.aspx too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>