September 1, 2009 at 6:42 pm
-- How to get the entire blocking details in the backend server..?
Using sp_who2 'active', may huge huge list of log-in credentials..but all of them actually are not creating the blockings. Also we have a seperate column: blkby to check the details, but this is not that convenient.
Any other T-SQL query is available to check the details?
Both in, SQL 2000 and SQL 2005?
Thanks.
September 2, 2009 at 3:51 am
Sourav (9/1/2009)
-- How to get the entire blocking details in the backend server..?Using sp_who2 'active', may huge huge list of log-in credentials..but all of them actually are not creating the blockings. Also we have a seperate column: blkby to check the details, but this is not that convenient.
Any other T-SQL query is available to check the details?
Both in, SQL 2000 and SQL 2005?
sp_who2 is how most dba's check for blocking. you can query some of the system tables like sysprocesses, but using sp_who2 is the easiest way, or use activity monitor.
you could always create a temporary table then run an execute sp_who2 into that table and then filter it for blocking.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 2, 2009 at 5:00 am
Sp_who2 can help you when you don’t have many processes on the server that are being blocked. On a busy server you might have a big blocking chain (which are hard to fallow with the output of sp_who2’s output). Blocking chain is a situation where few sessions are being blocked by different sessions, but the cause of all the blocking is one specific session. For example – suppose that process 55 is being blocked by process 52. Process 57 is being blocked by process 55. In this situation process 52 is causing a blocking chain and it is the blocking chain header. In order to see which spid is a chain header you can run the following statement (which works on SQL Server 2000 and SQL Server 2005:
select blocking.spid
from master.dbo.sysprocesses blocking inner join master.dbo.sysprocesses blocked
on blocking.spid = blocked.blocked
where blocking.blocked = 0
If you are working with SQL Server 2005, you can use the DMV sys.dm_os_waiting_tasks:
select blocking.blocking_session_id
from sys.dm_os_waiting_tasks blocking inner join sys.dm_os_waiting_tasks blocked
on blocking.session_id = blocked.blocking_session_id
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 2, 2009 at 12:08 pm
Thanks Adi..The response was quite vivid. 🙂
Thanks.
September 3, 2009 at 7:24 am
check out sp_WhoIsActive, an AMAZINGLY capable sproc from Adam Machanic.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 3, 2009 at 8:35 am
Hi,
You can try this ....
SELECT
t1.resource_type,
'database' = DB_NAME(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
t2.wait_duration_ms,
(SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
(CASE WHEN t3.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE t3.statement_end_offset
END - t3.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
t2.resource_description
FROM
sys.dm_tran_locks AS t1,
sys.dm_os_waiting_tasks AS t2,
sys.dm_exec_requests AS t3
WHERE
t1.lock_owner_address = t2.resource_address AND
t1.request_request_id = t3.request_id AND
t2.session_id = t3.session_id
Hope this helps...
Thanks,
\\K 🙂
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
September 3, 2009 at 12:42 pm
I wrote the following sp_block stored procedure to display blocking processes and the processes that they block. I add the stored procedure to the master database of all new SQL Server instances and I have created a keyboard shortcut in SSMS to run the query using CTRL-3.
The query uses recursion to present the data in an easily-readable format. It uses a recursive Common Table Expression so will only work under SQL Server 2005 onwards.
Chris
IF OBJECT_ID('dbo.sp_block', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_block
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.sp_block
AS
--------------------------------------------------------------------
-- dbo.sp_block
--------------------------------------------------------------------
-- Author:C Howarth
-- Date:20090331
-- Database:master
--
-- Description
-- -----------
-- Returns the blocked process hierarchy for any blocked processes.
--
-- History
-- -------
--
--------------------------------------------------------------------
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
SELECT es.session_id,
ISNULL(blocking_session_id, 0) AS blocking_session_id,
es.host_name AS host_name,
es.original_login_name AS login_name,
es.program_name,
last_wait_type,
es.cpu_time,
es.logical_reads + es.writes AS physical_io,
DB_NAME(er.database_id) as database_name,
CASE WHEN er.statement_start_offset = 0
AND er.statement_end_offset = 0
THEN st.text
WHEN er.statement_start_offset 0
AND er.statement_end_offset = -1
THEN RIGHT(st.text, LEN(st.text) - (er.statement_start_offset / 2) + 1)
WHEN er.statement_start_offset 0
AND er.statement_end_offset - 1
THEN SUBSTRING(st.text, (er.statement_start_offset / 2) + 1, (er.statement_end_offset / 2) - (er.statement_start_offset / 2))
ELSE st.text
END AS sql_text_statement,
wait_time,
st.text AS sql_text,
qp.query_plan
INTO #tmp
FROM sys.dm_exec_sessions es
LEFT JOIN (sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
)ON er.session_id = es.session_id
LEFT JOIN (sys.dm_exec_connections ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
)ON ec.session_id = es.session_id
;WITH CTE
AS
(
SELECT session_id AS RootBlockingSPID,
session_id,
blocking_session_id,
0 AS nestlevel,
CAST(session_id AS VARCHAR(MAX)) AS blocking_chain,
host_name,
login_name,
program_name,
last_wait_type,
cpu_time,
physical_io,
wait_time,
database_name,
sql_text_statement,
sql_text,
query_plan
FROM #tmp sp
WHERE blocking_session_id = 0
UNION ALL
SELECT CTE.RootBlockingSPID,
sp.session_id,
sp.blocking_session_id,
CTE.nestlevel + 1,
blocking_chain + ' <-- ' + CAST(sp.session_id AS VARCHAR(MAX)),
sp.host_name,
sp.login_name,
sp.program_name,
sp.last_wait_type,
sp.cpu_time,
sp.physical_io,
sp.wait_time,
sp.database_name,
sp.sql_text_statement,
sp.sql_text,
sp.query_plan
FROM #tmp sp
INNER JOIN CTE
ON CTE.session_id = sp.blocking_session_id
),
CTE2
AS
(
SELECT RootBlockingSPID,
session_id,
blocking_session_id,
blocking_chain,
host_name,
login_name,
program_name,
last_wait_type,
cpu_time,
physical_io,
wait_time,
database_name,
sql_text_statement,
sql_text,
query_plan
FROM CTE
WHERE EXISTS (SELECT 1 FROM CTE CTE2 WHERE CTE2.blocking_session_id = CTE.session_id)
AND blocking_session_id = 0
UNION ALL
SELECT RootBlockingSPID,
session_id,
blocking_session_id,
blocking_chain,
host_name,
login_name,
program_name,
last_wait_type,
cpu_time,
physical_io,
wait_time,
database_name,
sql_text_statement,
sql_text,
query_plan
FROM CTE
WHERE blocking_session_id 0
)
SELECT session_id,
blocking_chain,
host_name,
login_name,
program_name,
database_name,
wait_time,
last_wait_type,
cpu_time,
physical_io,
sql_text_statement,
sql_text,
query_plan
FROM CTE2
ORDER BY RootBlockingSPID,
blocking_chain
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply