Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Return Query Text Along With sp_who2 Using Dynamic Management Views Expand / Collapse
Author
Message
Posted Wednesday, February 4, 2009 12:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 7, 2010 3:05 PM
Points: 6, Visits: 38
I added stored proc drop and create statements, meaning you run the following to create a proc, then just exec sp_who2DMV...

USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_who2DMV]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_who2DMV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_who2DMV]
AS
SELECT D.text SQLStatement, A.Session_ID SPID, ISNULL(B.status,A.status) Status, A.login_name Login,
A.host_name HostName, C.BlkBy, DB_NAME(B.Database_ID) DBName, B.command, ISNULL(B.cpu_time, A.cpu_time) CPUTime,
ISNULL((B.reads + B.writes),(A.reads + A.writes)) DiskIO, A.last_request_start_time LastBatch, A.program_name
FROM
sys.dm_exec_sessions A
LEFT JOIN
sys.dm_exec_requests B
ON A.session_id = B.session_id
LEFT JOIN
(
SELECT
A.request_session_id SPID,
B.blocking_session_id BlkBy
FROM sys.dm_tran_locks as A
INNER JOIN sys.dm_os_waiting_tasks as B
ON A.lock_owner_address = B.resource_address
) C
ON A.Session_ID = C.SPID
OUTER APPLY sys.dm_exec_sql_text(sql_handle) D
WHERE A.Session_ID > 50;


Columbus, GA SQL Server User Group
http://columbusga.sqlpass.org/
Post #650209
Posted Monday, April 13, 2009 10:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 7, 2013 7:07 AM
Points: 26, Visits: 19
As a .NET developer using SQL Server exclusively I have found that it is very easy to forget to close connections when using datasets with table adapters or readers. Due to this I needed a way to find the SQL text for the IDLE connections that were orphaned by my application. This script provided 99% of what I needed. THANK YOU!!!! Here is my updated script which also joins the sys.dm_exec_connections view to find the last SQL command run on the connections. In this way I can find the code responsible for the call to SQL and add the required closes for the connections.

SELECT
t.text AS SQLStatement,
s.Session_ID AS SPID,
COALESCE(r.status, s.status) AS Status,
s.login_name AS Login,
s.host_name AS HostName,
lw.BlkBy AS BlockedBy,
DB_NAME(r.Database_ID) AS DBName,
r.command AS Command,
COALESCE(r.cpu_time, s.cpu_time) AS CPUTime,
COALESCE((r.reads + r.writes), (s.reads + s.writes)) AS DiskIO,
s.last_request_start_time AS LastBatch,
s.program_name AS ProgramName
FROM
sys.dm_exec_sessions AS s
LEFT JOIN
sys.dm_exec_requests AS r
ON
s.session_id = r.session_id
LEFT JOIN
sys.dm_exec_connections AS c
ON
s.Session_ID = c.Session_ID
LEFT JOIN
(
SELECT
l.request_session_id AS SPID,
w.blocking_session_id AS BlkBy
FROM
sys.dm_tran_locks as l
INNER JOIN
sys.dm_os_waiting_tasks as w
ON
l.lock_owner_address = w.resource_address
) AS lw
ON
s.Session_ID = lw.SPID
OUTER APPLY
sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS t

Thanks again!!!!!
Post #696298
Posted Monday, June 7, 2010 3:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 7, 2010 3:05 PM
Points: 6, Visits: 38
> a year later, ha... I ran across this thread in a search and realized I never came back and posted the version I've been using since shortly after my previous sp_who2DMV post.... This version compiles the work from KenSimmons and Grasshopper into one proc. It also accepts a parm... For example you can enter sp_who2DMV 'CPU' to sort desc by highest CPU. See comments for more.

USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_who2DMV]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_who2DMV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_who2DMV]
@ORDERBY VARCHAR(4) = 'SPID'
/*
The @ORDERBY parameter supports:
"CPU" CPUTime
"IO" DiskIO
"USR" Login (user)
"HOST" HostName
"APP" AppName
Execution examples:
EXEC sp_who2DMV --No order by (orders by SPID by default)
EXEC sp_who2DMV 'CPU' --orders by highest CPU Time
EXEC sp_who2DMV 'IO' --orders by highest Disk IO
*/
AS
IF ((SELECT
CASE
WHEN @ORDERBY in ('SPID', 'CPU', 'IO', 'USR', 'HOST', 'APP')
THEN 1
ELSE 0
END) = 0)
BEGIN
-- abort if invalid @ORDERBY parameter entered
RAISERROR('@ORDERBY parameter not SPID, CPU, IO, USR, HOST or APP',11,1)
RETURN
END
SELECT
t.text AS SQLStatement,
s.Session_ID AS SPID,
COALESCE(r.status, s.status) AS Status,
s.login_name AS Login,
s.host_name AS HostName,
lw.BlkBy AS BlockedBy,
DB_NAME(r.Database_ID) AS DBName,
r.command AS Command,
COALESCE(r.cpu_time, s.cpu_time) AS CPUTime,
COALESCE((r.reads + r.writes), (s.reads + s.writes)) AS DiskIO,
s.last_request_start_time AS LastBatch,
s.program_name AS ProgramName
FROM
sys.dm_exec_sessions AS s
LEFT JOIN
sys.dm_exec_requests AS r
ON
s.session_id = r.session_id
LEFT JOIN
sys.dm_exec_connections AS c
ON
s.Session_ID = c.Session_ID
LEFT JOIN
(
SELECT
l.request_session_id AS SPID,
w.blocking_session_id AS BlkBy
FROM
sys.dm_tran_locks as l
INNER JOIN
sys.dm_os_waiting_tasks as w
ON
l.lock_owner_address = w.resource_address
) AS lw
ON
s.Session_ID = lw.SPID
OUTER APPLY
sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS t
WHERE s.Session_ID > 50
ORDER BY
CASE
WHEN @ORDERBY = 'CPU' THEN cast(ISNULL(r.cpu_time, s.cpu_time) as varchar)
WHEN @ORDERBY = 'IO' THEN cast(ISNULL((r.reads + r.writes),(s.reads + s.writes)) as varchar)
WHEN @ORDERBY = 'USR' THEN s.login_name
WHEN @ORDERBY = 'HOST' THEN s.host_name
WHEN @ORDERBY = 'APP' THEN s.program_name
END DESC


Columbus, GA SQL Server User Group
http://columbusga.sqlpass.org/
Post #933699
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse