View Active Session SQL

  • Comments posted to this topic are about the item View Active Session SQL

  • 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

  • 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

  • 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!

  • 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.

  • 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????

  • 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!

  • 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

  • Awesome! Thanks!

  • 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