MSSQL, Tips and TricksMSSQL: Session and Request info, Blocking sessions

MSSQL: Session and Request info, Blocking sessions

Here is a list of queries that can be helpful if you are trying to troubleshoot locks and blocking sessions on SQL server.


-- to get session and connections on the server
SELECT *
FROM sys.dm_exec_sessions AS sess
JOIN sys.dm_exec_connections AS conn ON ( sess.session_id = conn.session_id );


-- another command to see details
EXEC sp_who2
EXEC sp_who2 'active'


-- to find all requests and who is blokcing whom
-- see the column = blocking_session_id
SELECT * FROM sys.dm_exec_requests


-- show the longest running SPIDs
-- Till spid 50,it's all are sql server internal process sessions
select
P.spid
, right(convert(varchar, dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 121), 12) as 'batch_duration'
, P.program_name
, P.hostname
, P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and P.status not in ('background', 'sleeping')
and P.cmd not in (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
)
order by batch_duration desc


-- to see the SQL running for a given spid
declare
@spid int
, @stmt_start int
, @stmt_end int
, @sql_handle binary(20)

set @spid = XXX — Fill this in

select top 1
@sql_handle = sql_handle
, @stmt_start = case stmt_start when 0 then 0 else stmt_start / 2 end
, @stmt_end = case stmt_end when -1 then -1 else stmt_end / 2 end
from master.dbo.sysprocesses
where spid = @spid
order by ecid

SELECT
SUBSTRING( text,
COALESCE(NULLIF(@stmt_start, 0), 1),
CASE @stmt_end
WHEN -1
THEN DATALENGTH(text)
ELSE
(@stmt_end – @stmt_start)
END
)
FROM ::fn_get_sql(@sql_handle)

Categories: MSSQL, Tips and Tricks

Comments

No Comments Yet. Be the first?

Post a comment

Your email address will not be published. Required fields are marked *