January 22, 2014 at 9:26 pm
Comments posted to this topic are about the item Modified SP_WHOM
January 23, 2014 at 8:09 am
Could the author clarify the business or technical purpose of this script?
January 23, 2014 at 8:26 am
This script is used for tracking blocking issues or just monitoring sessions on any database server.
Cheers
January 23, 2014 at 8:33 am
I've got a Stored Procedure some what like this but I've also added:
DBCC InputBuffer(<SPId Variable>);
for each SPId in the result set. It helps to know what command was/is executing.
One of the problems with this type of routine is that work comes and goes on a busy system such that in between the separate queries you have to do to collect all the relevant information some of the SPIds may drop or reconnect messing up the consistency of the collected information. But this doesn't seem to happen very often.
Ha! Love the grammatically correct procedure name!
January 23, 2014 at 9:51 am
That is a great point! I have the following similar version for those interested.
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_whom1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_whom1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_whom1] AS
--------------------------------------------------------------------------------------------------
--
-- Script Name: GetSessionSQLText.sql
--
-- Author: David Kranes
--
-- Description: This script will return each spid and it's corresponding SQL text. Supply
-- desired SPID or ALL non-system SPID's will be returned.
--
-- Parameters:
-- 1) @desSPID - Limits results to just this SPID. Set below in the script.
--
-- Revision History:
-- 1) 1/26/2012 - [DBK] - Created.
--
--------------------------------------------------------------------------------------------------
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(25),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
sqlcmd NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)
DECLARE @spID smallint,
@Blocked VARCHAR(25),
@sqltext VARCHAR(max),
@sqlcmd VARCHAR(max),
@physio BIGINT,
@hostName VARCHAR(2000),
@desSPID SMALLINT,
@dbName VARCHAR(100),
@Cmd VARCHAR(100),
@status VARCHAR(100),
@programName VARCHAR(1000),
@loginTime DATETIME,
@lastBatch DATETIME
-- Set desired SPID. If NULL, it will return all non-system SPID's.
-- SET @desSPID = 4704
BEGIN TRY
IF @desSPID IS NOT NULL
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid = @desSPID
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
ELSE
DECLARE spID_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR SELECT spid, CASE blocked WHEN 0 THEN 'NO' ELSE 'YES BY SPID: ' + CAST(blocked AS VARCHAR) END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid > 50 and spid <> @@SPID and status IN ('running', 'runnable', 'suspended')
GROUP BY spid, blocked, physical_io, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
ORDER BY spid
OPEN spID_cursor
FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch
WHILE @@FETCH_STATUS = 0
BEGIN
/*
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @spID
*/
SELECT
@sqltext = A.text,
@sqlcmd = SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text)ELSE sp.stmt_end END - sp.stmt_start )/2)
FROM
sys.sysprocesses sp
OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) as A
WHERE
spid = @spID
INSERT INTO #curSQL
(spID, blocked, hostName, dbName, cmd, sqlText, sqlcmd, phyIO, Status, programName, loginTime, lastBatch)
VALUES(@spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physio, @status, @programName, @loginTime, @lastBatch)
-- SELECT @spID, @Blocked, @hostName, @dbName, @cmd, @sqltext, @sqlcmd, @physio, @status, @programName, @loginTime, @lastBatch
--FROM ::fn_get_sql(@sqltext)
FETCH NEXT
FROM spID_cursor
INTO @spID, @Blocked, @hostName, @dbName, @cmd, @physio, @status, @programName, @loginTime, @lastBatch
END
CLOSE spID_cursor
DEALLOCATE spID_cursor
SELECT * FROM #curSQL ORDER BY blocked desc, spid
-- If there is blocking, print out blocking info.
IF (SELECT sum(blocked) from sys.sysprocesses WHERE spid > 50) > 0
--IF (SELECT TOP (1) COUNT(*) FROM #curSQL WHERE blocked = 'YES') > 0
BEGIN
/*SELECT x.session_id AS 'Blocked Session ID',
(
-- Query gets XML text for the sql query for the session_id
SELECT text AS [text()]
FROM sys.dm_exec_sql_text(x.sql_handle)
FOR XML PATH(''), TYPE
)AS 'Blocked SQL Text',
x.host_name,
x.login_name,
x.start_time,
x.totalReads,
x.totalWrites,
x.totalCPU,
x.writes_in_tempdb,
COALESCE(x.blocking_session_id, 0) AS 'Blocking Session ID',
(
SELECT p.text
FROM
(
-- Query gets the corresponding sql_handle info to find the XML text in the next query
SELECT MIN(sql_handle) AS sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = x.blocking_session_id
) AS r_blocking
CROSS APPLY
(
-- Query will pull back the XML text for a blocking session if there is any from the sql_haldle
SELECT text AS [text()]
FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
) AS 'Blocking Text'
FROM
(
-- Query returns active session_id and metadata about the session for resource, blocking, and sql_handle
SELECT r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id,
SUM(r.reads) AS totalReads,
SUM(r.writes) AS totalWrites,
SUM(r.cpu_time) AS totalCPU,
SUM(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
--JOIN #curSQL cs ON r.session_id = cs.spID
WHERE r.status IN ('running', 'runnable', 'suspended') AND
r.blocking_session_id <> 0 AND
r.session_id <> @@SPID
GROUP BY r.session_id,
s.host_name,
s.login_name,
r.start_time,
r.sql_handle,
r.blocking_session_id
) x */
select
'LEAD BLOCKER: ' + CAST(spid as varchar(20)), loginame, hostname, cpu, memusage, physical_io, *
from
master..sysprocesses a
where
exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.blocked = a.spid ) and not
exists ( select b.*
from master..sysprocesses b
where b.blocked > 0 and
b.spid = a.spid )
order by spid
END-- If there is blocking, print out blocking info.
END TRY
BEGIN CATCH
CLOSE spID_cursor
DEALLOCATE spID_cursor
SELECT ERROR_NUMBER() ErrorNBR, ERROR_SEVERITY() Severity, ERROR_LINE() ErrorLine, ERROR_MESSAGE() Msg
PRINT 'There was an error in the script.'
END CATCH
Cheers
January 23, 2014 at 10:02 am
FYI,
DBCC InputBuffer(54);
is problematic. It may be better to use the following as it can be used in a row set query:
Select
s.session_id[SPId]
,SubString
(
st.text,
(r.statement_start_offset/2)+1,
((Case r.statement_end_offsetwhen -1 then DataLength(st.text)else r.statement_end_offset End-r.statement_start_offset)/2)+1
)[Command String]
from sys.dm_exec_sessions as s
join sys.dm_exec_requests as r on r.session_id = s.session_id
cross apply sys.dm_exec_sql_text(r.sql_handle) as st
order by s.last_request_end_time;
🙂
January 23, 2014 at 2:23 pm
Thanks. I combined this script with what Peter wrote to get information about what's currently active.
October 21, 2015 at 5:46 am
What advantages does this have over sp_whoIsActive?
October 21, 2015 at 6:36 am
oradbguru (1/23/2014)
This script is used for tracking blocking issues or just monitoring sessions on any database server.
Great, I'll give it a try.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply