Creating sp_who5, need advice
-
I would have tried it the way you were running it. Not sure there's a way outside a cursor with dbcc inputbuffer. Course there are a lot of DMVs, one might provide the same data.
What differences are you seeing?
-
Steve Jones - Editor (1/25/2008)
I would have tried it the way you were running it. Not sure there's a way outside a cursor with dbcc inputbuffer. Course there are a lot of DMVs, one might provide the same data.What differences are you seeing?
For example ...
Say I have a connection to a server that is running: "exec admin.dbo.sp_who5"
If I DBCC INPUTBUFFER that spid, I see "exec admin.dbo.sp_who5" <- This is what I would expect to see.
If I look at the results from the code above, the newly created sp_who5, it shows the contents of the procedure ... as a create statement instead of that I was simply executing it.
Make sense?
-
I have some code I wrote you can have. I had to put it in a cursor for my situation, but you shouldn't have to. What you'll want to do is join back to sysobjects and that'll tell you what objects are being called. So the code I have may not be exactly what you're looking for but it'll point you in the right direction.
SELECT TOP 20 st.DBId, st.objectID, total_worker_time/execution_count AS [AvgCPUTime],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
INTO #Table
FROM master.sys.dm_exec_query_stats AS qs
CROSS APPLY master.sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
--select * from #Table
CREATE TABLE ##Objects
(
DBId int,
ObjectID int,
ObjectName varchar(100)
)
DECLARE @currDBId int,
@currObjectID bigint,
@SQL nvarchar(200)
DECLARE Queries CURSOR
READ_ONLY
FOR select DBId, ObjectID from #Table
OPEN Queries
FETCH NEXT FROM Queries INTO @currDBId, @currObjectID
WHILE (@@fetch_status <> -1)
BEGIN
SET @SQL = 'USE ' + DB_Name(@currDBId) + '; Insert ##Objects Select ' + CAST(@currDBId as varchar(10)) + ', object_id, Name from sys.objects '
+ 'where object_id = ' + CAST(@currObjectID as varchar(10))
EXEC (@SQL)
--print @SQL
FETCH NEXT FROM Queries INTO @currDBId, @currObjectID
END
CLOSE Queries
DEALLOCATE Queries
Select o.ObjectName, DB_Name(t.DBId) as DBName, t.ObjectID, t.[AvgCPUTime], t.statement_text
from #Table t
INNER JOIN ##Objects o
on t.objectID = o.objectID
order by t.avgcputime desc
drop table #Table
drop table ##Objects
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
OBJECT_NAME() takes two parameters, the second being an optional database id. Both values are available in sys.dm_exec_sql_text.
For example:
SELECT r.session_id, r.wait_type, r.wait_time, r.reads, r.writes, r.logical_reads, r.cpu_time,
IsNull(OBJECT_NAME(st.objectid, st.dbid), 'Ad hoc') AS ObjectName, --st.text
CASE WHEN r.statement_end_offset > 0
THEN Substring([Text], r.statement_start_offset/2, r.statement_end_offset/2 - r.statement_start_offset/2)
ELSE CONVERT(varchar(8000), ISNULL([Text], '')) END
FROM sys.dm_exec_requests r CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) st
WHERE r.session_id > 45
-Eddie
Eddie Wuerch
MCM: SQL -
Adam Bean (1/25/2008)If I look at the results from the code above, the newly created sp_who5, it shows the contents of the procedure ... as a create statement instead of that I was simply executing it.
Make sense?
I figured out a way to get the Object Name rather than the text of the stored Proc but it is a nasty work around. I will just put the things that I changed.
CREATE PROCEDURE [dbo].[sp_who5]
AS
SET NOCOUNT ON
SELECT
p.spid AS [SPID],
db_name(p.dbid) AS [DBName],
isNULL(x.name,st.text) AS [Query],
p.status AS [Status], ......
Continue rest of the query as same till
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) as st
LEFT OUTER JOIN
(Select id, name from sys.sysobjects with (readuncommitted)
UNION ALL
Select id, name from .sys.sysobjects with (readuncommitted)
UNION ALL
Select id, name from .sys.sysobjects with (readuncommitted))
as x
on st.ObjectID = x.ID
ORDER BY p.blocked, db_name(p.dbid), p.cpu
Now what I am missing is, I want to see the parameters that are passed. How can I get that?
-Roy
-
Roy,
What is x aliasing?
-
I just created a derived table for all the Union ALL statements and aliased as x.
(Select id, name from sys.sysobjects with (readuncommitted)
UNION ALL
Select id, name from testdb.sys.sysobjects with (readuncommitted)
UNION ALL
Select id, name from adventureworks.sys.sysobjects with (readuncommitted)) as x
Since we have the ID, we can get the name of the Object from this derived query. We could actually create a view for this derived table as well and use the view name instead of all the Union ALL's
-Roy
-
Roy,
Why would you go through the trouble of the UNION ALL view? Does OBJECT_NAME() not work for you?
-Eddie
Eddie Wuerch
MCM: SQL -
It might work for me, I have to check it. It was just a work around that I had for getting the Stored Proc name. When I go back to office, I will try with that.
-Roy
-
Drop Procedure dbo.sp_who5
go
/*******************************************************************************************************
** Name: admin.dbo.sp_who5
** Desc: Procedure to retrieve information about active sql connections
** Auth: Adam Bean
** Date: 12/10/2007
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- ---------------------------------------
**
********************************************************************************************************/
CREATE PROCEDURE [dbo].[sp_who5]
AS
SET NOCOUNT ON
SELECT
p.spid AS [SPID],
db_name(p.dbid) AS [DBName],
isNULL(x.name,st.text) AS [Query],
--x.name as [ObjName],
p.status AS [Status],
p.blocked AS [BlkBy],
ISNULL(t.trancount,0) AS [TranCount],
ISNULL(tl.lockcount,0) AS [LockCount],
p.loginame AS [Login],
p.hostname AS [Host Name],
p.cpu AS [CPU],
p.physical_io AS [IO],
p.login_time AS [Start Time],
p.last_batch AS [Last Batch],
p.program_name AS [Program Name],
p.spid AS [SPID]
FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_connections c (NOLOCK)
ON c.session_id = p.spid
LEFT OUTER JOIN
(
SELECT
session_id,
database_id = MAX(database_id),
trancount = COUNT(*)
FROM
sys.dm_tran_session_transactions t
INNER JOIN
sys.dm_tran_database_transactions dt
ON
t.transaction_id = dt.transaction_id
GROUP BY
session_id
) t
ON t.session_id = p.spid
LEFT OUTER JOIN
(
SELECT
request_session_id,
database_id = MAX(resource_database_id),
lockcount = COUNT(*)
FROM
sys.dm_tran_locks WITH (NOLOCK)
GROUP BY
request_session_id
) tl
ON p.spid = tl.request_session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) as st
LEFT OUTER JOIN
(Select id, name from sys.sysobjects with (readuncommitted)
UNION ALL
Select id, name from AdventureWorks.sys.sysobjects with (readuncommitted)
UNION ALL
Select id, name from MyDb.sys.sysobjects with (readuncommitted))
as x
on st.ObjectID = x.ID
ORDER BY p.blocked, db_name(p.dbid), p.cpu
GO
SET NOCOUNT OFF
-Roy
-
I went back and changed it so that it does the dbcc inputbuffer ... I wasn't able to find any other way to get the actual procedure called, vs the creation of the procedure. I still have more work to do to this proc, but for now, it gets the job done:
IF OBJECT_ID('sp_who5','P') IS NOT NULL
DROP PROCEDURE sp_who5
GO
/*******************************************************************************************************
**Name:admin.dbo.sp_who5
**Desc:Procedure to retrieve information about active sql connections
**Auth:Adam Bean
**Date:12/10/2007
*******************************************************************************
**Change History
*******************************************************************************
**Date:Author:Description:
**-------------------------------------------------------
** 12.18.07MJSWrapped ISNULL around Tran and Lock Count
** 02.13.08ASBUsing DBCC INPUTBUFFER to query information instead of DMV's
** 02.14.08ASBProcedure will no longer return session running procedure
** 02.14.08ASBSuppressed informational messages on DBCC output
*******************************************************************************
**ToDo
*******************************************************************************
- Passed in parameters for ordering
********************************************************************************************************/
CREATE PROCEDURE [dbo].[sp_who5]
AS
SET NOCOUNT ON
DECLARE
@SPID INT
,@SQLID INT
-- Setup table to hold data
DECLARE @Activity TABLE
(
[SPID][SMALLINT] NOT NULL,
[DBName][NVARCHAR](128) NULL,
[Query][NVARCHAR](MAX) NULL,
[Status][NCHAR](30) NOT NULL,
[BlkBy][SMALLINT] NOT NULL,
[TranCount][INT] NOT NULL,
[LockCount][INT] NOT NULL,
[Login][NCHAR](128) NOT NULL,
[Host Name][NCHAR](128) NOT NULL,
[CPU][INT] NOT NULL,
[IO][BIGINT] NOT NULL,
[Start Time][DATETIME] NOT NULL,
[Last Batch][DATETIME] NOT NULL,
[Program Name][NCHAR](128) NOT NULL
)
-- Insert data
INSERT INTO @Activity
([SPID], [DBName], [Status], [BlkBy], [TranCount], [LockCount], [Login], [Host Name], [CPU], [IO], [Start Time], [Last Batch], [Program Name])
SELECT
p.spidAS [SPID],
db_name(p.dbid)AS [DBName],
p.statusAS [Status],
p.blockedAS [BlkBy],
ISNULL(t.trancount,0)AS [TranCount],
ISNULL(tl.lockcount,0)AS [LockCount],
p.loginameAS [Login],
p.hostnameAS [Host Name],
p.cpuAS [CPU],
p.physical_ioAS [IO],
p.login_timeAS [Start Time],
p.last_batchAS [Last Batch],
p.program_nameAS [Program Name]
FROM sys.sysprocesses p
INNER JOIN sys.dm_exec_connections c (NOLOCK)
ON c.session_id = p.spid
LEFT OUTER JOIN
(
SELECT
session_id,
database_id = MAX(database_id),
trancount = COUNT(*)
FROM
sys.dm_tran_session_transactions t
INNER JOIN
sys.dm_tran_database_transactions dt
ON
t.transaction_id = dt.transaction_id
GROUP BY
session_id
) t
ON t.session_id = p.spid
LEFT OUTER JOIN
(
SELECT
request_session_id,
database_id = MAX(resource_database_id),
lockcount = COUNT(*)
FROM
sys.dm_tran_locks WITH (NOLOCK)
GROUP BY
request_session_id
) tl
ON p.spid = tl.request_session_id
WHERE p.spid <> @@SPID
ORDER BY p.blocked, db_name(p.dbid), p.cpu
-- Setup table to hold spid info
DECLARE @QueryCmd TABLE
(
SQLID[INT] IDENTITY,
SPID[INT],
EventType[VARCHAR](100),
Parameters[INT],
Command[VARCHAR](MAX)
)
-- Get DBCC INPUTBUFFER per spid
DECLARE spids CURSOR FOR
SELECT spid FROM @Activity
OPEN spids
FETCH NEXT FROM spids INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @QueryCmd
(EventType, Parameters, Command)
EXEC('DBCC INPUTBUFFER( ' + @SPID + ') WITH NO_INFOMSGS')
SELECT @SQLID = MAX(SQLID) FROM @QueryCmd
UPDATE @QueryCmd
SET spid = @SPID
WHERE SQLID = @SQLID
FETCH NEXT FROM spids INTO @SPID
END
CLOSE spids
DEALLOCATE spids
-- Update main table with new spid info
UPDATE a
SET a.Query = q.command
FROM @Activity a
JOIN @QueryCmd q
ON a.spid = q.spid
-- Return results
SELECT * FROM @Activity
SET NOCOUNT OFF
-
Hey Adam,
That looks cool. I tried it out and works good.
-Roy
-
If anyone is interested, I did some reworking ... I still have a small problem on a join where I'm missing out on nulls and dynamic SQL, but I have both a proc version that uses DBCC INPUTBUFFER, and a DMV version for a view.
-
this is great
i'm going to look at the earlier sp_who code and see where it passes the status to it. i have one server that always has over 1500 connections and i always run sp_who2 active on it because i only want the active connections
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply