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

New SP_WHO3 Expand / Collapse
Author
Message
Posted Monday, June 14, 2010 5:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:29 AM
Points: 129, Visits: 510
Hello all,
I just did this sp_who and I think some of you will find it useful, You can provide it with three parameter ( it is shielded against code injection too...)
1) spid
2) databasename
3) session status

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 06/14/2010 16:17:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_who3] (
@SessionID int = NULL ,
@DBID varchar(100) = NULL ,
@statusID varchar(100) = NULL
)
AS
BEGIN

DECLARE @sSql nvarchar(4000)
DECLARE @sWhereClause nvarchar(4000)
DECLARE @sFootClause nvarchar(4000)
DECLARE @ParmDefinition nvarchar(4000)
DECLARE @NewLine nvarchar(4000)
SET @NewLine = CHAR(13) + CHAR(10)
SET @sWhereClause = '' -- Initialise

SET @sSql = 'SELECT ' + @NewLine
+ ' SPID = e.spid, ' + @NewLine
+ ' Status = max(A.status), ' + @NewLine
+ ' waits = sum(e.waittime), ' + @NewLine
+ ' [Login] = max(A.login_name), ' + @NewLine
+ ' HostName = max(A.host_name), ' + @NewLine
+ ' BlkBy = max(c.blocking_session_id), ' + @NewLine
+ ' DBName = max(DB_Name(e.dbid)), ' + @NewLine
+ ' Command = max(e.cmd), ' + @NewLine
+ ' SQLStatement = max(isnull(substring(b.text,1,4000),'''')), ' + @NewLine
+ ' ObjectName = max(OBJECT_NAME(b.objectid)), ' + @NewLine
+ ' Programname = max(A.Program_name), ' + @NewLine
+ ' ElapsedMS = max(C.total_elapsed_time), ' + @NewLine
+ ' CPUTime = sum(isnull(e.cpu,0)), ' + @NewLine
+ ' IOReads = max(isnull(a.logical_reads + a.reads,0)), ' + @NewLine
+ ' IOWrites = max(isnull(a.writes,0)), ' + @NewLine
+ ' LastWaitType = max(e.lastwaittype), ' + @NewLine
+ ' LastBatch = max(e.last_batch), ' + @NewLine
+ ' DiskIO = sum(e.physical_io) ' + @NewLine
+ ' FROM sys.dm_exec_sessions (nolock) a ' + @NewLine
+ ' LEFT JOIN sys.dm_exec_requests (nolock) c ON c.session_id = a.session_id ' + @NewLine
+ ' LEFT JOIN sys.sysprocesses E (nolock) ON e.spid = a.session_id ' + @NewLine
+ ' OUTER APPLY sys.dm_exec_sql_text(e.sql_handle) b ' + @NewLine
+ ' LEFT JOIN sys.dm_exec_connections (nolock) d ON d.session_id = a.session_id ' + @NewLine
+ ' where a.session_id > 50 '+ @NewLine

if @SessionID IS NOT NULL
SET @sWhereClause= @sWhereClause+@NewLine+ ' AND a.session_id = @SessionID'

IF @DBID IS NOT NULL
SET @sWhereClause= @sWhereClause+@NewLine+ ' AND DB_Name(e.dbid) = @DBID'


IF @StatusID IS NOT NULL
SET @sWhereClause= @sWhereClause+@NewLine+ ' AND a.status = @StatusID'

SET @sFootClause = ' group by e.spid ORDER BY e.spid'

SET @ParmDefinition = '@SessionID int,' + @NewLine
+ ' @DBID varchar(100),' + @NewLine
+ ' @StatusID varchar(100)'

SET @sSql = @sSql + @sWhereClause+@newLine + @sFootClause


EXEC sp_executesql @sSql,@ParmDefinition,
@SessionID=@SessionID,
@DBID=@DBID,
@StatusID=@StatusID
END

Regards

Post #937187
Posted Tuesday, June 14, 2011 6:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 04, 2014 8:01 AM
Points: 3, Visits: 132
doesn't work on case-sensitive servers
Post #1124915
Posted Wednesday, June 15, 2011 7:26 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 4,128, Visits: 5,837
no need to reinvent the wheel. use sp_whoisactive, an absolutely amazing freebie from Adam Machanic. See sqlblog.com for code (which is documented) and 30 different blog posts detailing it's power.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1125693
Posted Wednesday, June 15, 2011 8:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 4:29 AM
Points: 129, Visits: 510
mmm sp_whoisactive is quite nice, but my last version of sp_who3 ( the one posted here is one year old) has some more useful options
Post #1125788
Posted Thursday, June 16, 2011 7:06 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:45 PM
Points: 4,128, Visits: 5,837
Can you please share with us what those "more useful options" might be please?

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1126487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse