January 26, 2012 at 1:34 pm
Comments posted to this topic are about the item View Active Session SQL
February 23, 2012 at 1:24 pm
Hi --
I use the below query to get what is going on in the system now.. It shows the queries and sessions being run
select r.session_id,status,substring(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 else r.statement_end_offset end - r.statement_start_offset)/2) as query_text ,qt.dbid,qt.objectid,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads,r.scheduler_id from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(sql_handle) as qt where r.session_id > 50 order by r.scheduler_id, r.status, r.session_id
I posted this as this is a related topic.
Thanks
February 24, 2012 at 3:58 am
Not tested completely, but this type of query can be written not to use a temp table or cursor
example below
DECLARE @spid SMALLINT
-- Set desired SPID. If NULL, it will return all non-system SPID's.
--SET @spid = <Put desired SPID her to filter>
SELECT
spid,
CASE blocked WHEN 0 THEN 'NO' ELSE 'YES' END blocked,
hostname,
db_name(dbid) AS dbName,
cmd,
(SELECT TEXT FROM ::fn_get_sql(sql_handle)) AS sqlText,
physical_io as phyIO,
status as Status,
program_name as programName,
login_time as loginTime,
last_batch as lastBatch
FROM sys.sysprocesses
WHERE
(ISNUMERIC(@spid) = 1 and spid > 50) or spid = @spid
ORDER BY blocked asc, spid
Steven
February 24, 2012 at 7:56 am
This looks like what I've been trying to show on my SQL server, but I'm getting an error:
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 35
Must declare the scalar variable "@desSPID".
I left the declare line commented out, assuming it would just return all processes? But that's not working. Tried adding "SET @desSPID = 2352" for my SQL process, but that gives the same error.
Any help is greatly appreciated, thanks!
February 24, 2012 at 8:22 am
ericb1 (2/24/2012)
This looks like what I've been trying to show on my SQL server, but I'm getting an error:Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '?'.
Msg 137, Level 15, State 2, Line 35
Must declare the scalar variable "@desSPID".
I left the declare line commented out, assuming it would just return all processes? But that's not working. Tried adding "SET @desSPID = 2352" for my SQL process, but that gives the same error.
Any help is greatly appreciated, thanks!
Maybe put the declare line back in, but leave out anything with 'Set @desSPID = whatever' as this will then leave the declared variable @desSPID with value NULL thus returning all processes.
February 24, 2012 at 8:56 am
You can try this one I published today in SS central:
http://www.sqlservercentral.com/scripts/Administration/88041/
SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO
CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(3),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)
DECLARE @spID smallint,
????????@Blocked VARCHAR(3),
????????@sqltext VARBINARY(128),
????????@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 = <Put desired SPID her to filter>
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' 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' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
????????????FROM sys.sysprocesses
????????????WHERE spid > 50
????????????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
???????? INSERT INTO #curSQL
???????? SELECT @spID, @Blocked, @hostName, @dbName, @cmd, TEXT, @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 asc, spid
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????
February 24, 2012 at 9:01 am
I get the same error unfortunately. Also, I'm running this as a script in a query window in SQL Server Management Studio. Do I need to execute it as a stored procedure? Or run it against a particular database?
Maybe I'm just executing it wrong?
thanks!
February 24, 2012 at 9:14 am
ericb1 (2/24/2012)
I get the same error unfortunately. Also, I'm running this as a script in a query window in SQL Server Management Studio. Do I need to execute it as a stored procedure? Or run it against a particular database?Maybe I'm just executing it wrong?
thanks!
The original version had unicode stuff in it. Try pasting this one:
SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO
CREATE TABLE #curSQL
(spID SMALLINT,
blocked VARCHAR(3),
hostName VARCHAR(2000),
dbName VARCHAR(100),
cmd VARCHAR(100),
sqlText NTEXT,
phyIO BIGINT,
Status VARCHAR(100),
programName VARCHAR(1000),
loginTime DATETIME,
lastBatch DATETIME
)
DECLARE @spID smallint,
@Blocked VARCHAR(3),
@sqltext VARBINARY(128),
@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 = <Put desired SPID her to filter>
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' 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' END blocked, hostname, db_name(dbid), cmd, physical_io, status, program_name, login_time, last_batch
FROM sys.sysprocesses
WHERE spid > 50
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
INSERT INTO #curSQL
SELECT @spID, @Blocked, @hostName, @dbName, @cmd, TEXT, @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 asc, spid
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
February 24, 2012 at 9:20 am
Awesome! Thanks!
February 24, 2012 at 9:57 am
Here is another version that also gives you the lead blocker when blocking occurs. This is designed to be run in SSMS for SS 2005 thru 2008 r2.
SET NOCOUNT ON
GO
IF OBJECT_ID('tempdb..#curSQL') IS NOT NULL
DROP TABLE #curSQL
GO
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, 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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply