June 25, 2008 at 10:12 pm
Comments posted to this topic are about the item Return Query Text Along With sp_who2 Using Dynamic Management Views
June 26, 2008 at 12:45 am
Hi,
Could you please elaborate reg what you did in that query as I am not getting it?
You can please provide the acript for the tables you are using so that at least we can execute the query to see the result.
I am working in SQL server 2005.
Thanks & Regards,
SS
June 26, 2008 at 2:45 am
Here's a similar one I use which uses a neat trick (credit to Adam Machanic) that returns the statement as xml so it can be clicked and opened in a new window. Unfortunately it's xml encoded but very convenient.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 06/26/2008 09:40:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_who3]
(
@filter tinyint = 1,
@filterspid int = NULL
)
AS
SET NOCOUNT ON;
DECLARE @processes TABLE
(
spid int,
blocked int,
databasename varchar(256),
hostname varchar(256),
program_name varchar(256),
loginame varchar(256),
status varchar(60),
cmd varchar(128),
cpu int,
physical_io int,
[memusage] int,
login_time datetime,
last_batch datetime,
current_statement_parent xml,
current_statement_sub xml)
INSERT INTO @processes
SELECTsub.*
FROM
(
SELECT sp.spid,
sp.blocked,
sd.name,
RTRIM(sp.hostname) AS hostname,
RTRIM(sp.[program_name]) AS [program_name],
RTRIM(sp.loginame) AS loginame,
RTRIM(sp.status) AS status,
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.login_time,
sp.last_batch,
(
SELECT
LTRIM(st.text) AS [text()]
FOR XML PATH(''), TYPE
) AS parent_text,
(
SELECT
LTRIM(CASE
WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL
ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
END) AS [text()]
FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = sp.spid
FOR XML PATH(''), TYPE
) AS child_text
FROM sys.sysprocesses sp WITH (NOLOCK) LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
) sub INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid
ORDER BY
sub.spid
-- if specific spid required
IF @filterspid IS NOT NULL
DELETE @processes
WHERE spid <> @filterspid
-- remove system processes
IF @filter = 1 OR @filter = 2
DELETE @processes
WHERE spid < 51
OR spid = @@SPID
-- remove inactive processes
IF @filter = 2
DELETE @processes
WHERE status = 'sleeping'
AND cmd IN ('AWAITING COMMAND')
AND blocked = 0
SELECT spid,
blocked,
databasename,
hostname,
loginame,
status,
current_statement_parent,
current_statement_sub,
cmd,
cpu,
physical_io,
program_name,
login_time,
last_batch
FROM @processes
ORDER BY spid
RETURN 0;
June 26, 2008 at 5:15 am
Here is the script. I am not sure why it will not copy correctly from the article. When you copy the script from the article there is no space between "A.program_nameFROM".
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
June 26, 2008 at 5:24 am
I so wish this worked with 2000
June 26, 2008 at 5:38 am
I've not tried this because the production environment I work in is stuck in 2k... but nice short too-the-point article. Thanks. And, I agree... I wish it worked in 2k.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 26, 2008 at 5:45 am
Hi Ken,
It's giving error as follows:
Msg 321, Level 15, State 1, Line 28
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
Thanks,
SS
June 26, 2008 at 6:57 am
sheetalsh (6/26/2008)
Hi Ken,It's giving error as follows:
Msg 321, Level 15, State 1, Line 28
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
Thanks,
SS
I get this error as well.
Marc
June 26, 2008 at 7:18 am
How close to this script can you get in SQL Server 2000? I'm stuck there for awhile
Thanks!
June 26, 2008 at 7:18 am
I copied the code from the article verbatim
ran fine in my SQL 2005 boxes (build 3042 and 2047)
I am guessing it won't run in SQL 2000
Dan: your code has a WINK in it, ha ha... when it should be the closing bracket for the TABLE declaration
June 26, 2008 at 7:29 am
sheetalsh (6/26/2008)
Hi Ken,It's giving error as follows:
Msg 321, Level 15, State 1, Line 28
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
Thanks,
SS
Right Click on your database and select properties. Under the options tab the compatibility level has to be set to "SQL Server 2005 (90)" for it to work.
June 26, 2008 at 7:56 am
Great script. It is nice to have everything together when viewing the results and checking for blocking.
Dave Novak
June 26, 2008 at 8:55 am
did something similar a few months ago with the connections and sessions DMV's. it was very nice except took a lot of storage. if we did this on a regular basis it would probably run 200GB - 300GB a month for 2-3 servers. i dumped the data into a table on the local server and every 15 minutes had an SSIS job copy it to a reporting server and delete all data in the local table.
June 26, 2008 at 9:39 am
I'm digging this solution. I really like to be able to filter my results, so I took the code from Dan (nice code Dan) that used the trick from Adam Machanic, and turned it into a table valued function:
USE [master]
GO
/****** Object: UserDefinedFunction [dbo].[fn_sp_who4] Script Date: 06/26/2008 11:31:07 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_sp_who4]
(@filterspid int = NULL, @filter tinyint = 1)
RETURNS @processes TABLE
(
spid int,
blocked int,
databasename varchar(256),
hostname varchar(256),
current_statement_parent xml,
current_statement_sub xml,
program_name varchar(256),
loginame varchar(256),
status varchar(60),
cmd varchar(128),
cpu int,
physical_io int,
[memusage] int,
login_time datetime,
last_batch datetime
)
AS
BEGIN
INSERT INTO @processes
SELECT sub.*
FROM
(SELECT sp.spid,
sp.blocked,
sd.name,
RTRIM(sp.hostname) AS hostname,
( SELECT LTRIM(st.text) AS [text()]
FOR XML PATH(''), TYPE) AS parent_text,
( SELECT
LTRIM(CASE
WHEN LEN(COALESCE(st.text, '')) = 0 THEN NULL
ELSE SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
END) AS [text()]
FROM sys.dm_exec_requests er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
WHERE er.session_id = sp.spid
FOR XML PATH(''), TYPE) AS child_text,
RTRIM(sp.[program_name]) AS [program_name],
RTRIM(sp.loginame) AS loginame,
RTRIM(sp.status) AS status,
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.login_time,
sp.last_batch
FROM sys.sysprocesses sp (NOLOCK)
LEFT JOIN sys.sysdatabases sd WITH (NOLOCK) ON sp.dbid = sd.dbid
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
) sub
INNER JOIN sys.sysprocesses sp2 ON sub.spid = sp2.spid
ORDER BY sub.spid
-- if specific spid required
IF @filterspid IS NOT NULL
DELETE @processes
WHERE spid <> @filterspid
-- remove system processes
IF @filter = 1 OR @filter = 2
DELETE @processes
WHERE spid < 51 OR spid = @@SPID
-- remove inactive processes
IF @filter = 2
DELETE @processes
WHERE status = 'sleeping'
AND cmd IN ('AWAITING COMMAND')
AND blocked = 0
RETURN
END
Then you can run
SELECT ... from master.dbo.fn_sp_who4(@filterspid = , @filter = )
WHERE ...
or
SELECT * from master.dbo.fn_sp_who4(default,default) for the function parameter defaults
You could also technically remove the parameters entirely since it's a table valued function after all and just use the where clause in the SELECT.
It's rough, but I like it 😀
--John Vanda
SQL Junior DBA
June 26, 2008 at 9:47 am
Thanks John and Ken for these scripts. Both are good and are worth keeping it handy.
SQL DBA.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply