SQL Server Performance

sp_who2db - order by DB

Discussion in 'Contribute Your SQL Server Scripts' started by homebrew01, Aug 24, 2005.

  1. homebrew01 New Member

    -- <br />--' ************************************************************<br />--' Parameters:<br />--' Purpose: Like SP_WHO2 but sorts by DATABASE name, CPU Time<br />--' Notes:<br />--' ------------------------------------------------------------<br />--' Revision History <br />--' Pgmr Date Rev Desc <br />--' ---- ----- ---- -------------------------------<br />-- GAD 5-9-2005<br />--' **************************************************************<br /><br />CREATE PROCEDURE sp_who2db <br /> @loginame sysname = NULL<br />as<br /><br />set nocount on<br /><br />declare<br /> @retcode int<br /><br />declare<br /> @sidlow varbinary(85)<br /> ,@sidhigh varbinary(85)<br /> ,@sid1 varbinary(85)<br /> ,@spidlow int<br /> ,@spidhigh int<br /><br />declare<br /> @charMaxLenLoginName varchar(6)<br /> ,@charMaxLenDBName varchar(15)<br /> ,@charMaxLenCPUTime varchar(10)<br /> ,@charMaxLenDiskIO varchar(10)<br /> ,@charMaxLenHostName varchar(10)<br /> ,@charMaxLenProgramName varchar(10)<br /> ,@charMaxLenLastBatch varchar(10)<br /> ,@charMaxLenCommand varchar(10)<br /><br /> ,@charMaxLenStatusL varchar(10)<br /><br />declare<br /> @charsidlow varchar(85)<br /> ,@charsidhigh varchar(85)<br /> ,@charspidlow varchar(11)<br /> ,@charspidhigh varchar(11)<br /><br />--------<br /><br />select<br /> @retcode = 0 -- 0=good ,1=bad.<br /><br />--------defaults<br />select @sidlow = convert(varbinary(85), (replicate(char(0), 85)))<br />select @sidhigh = convert(varbinary(85), (replicate(char(1), 85)))<br /><br />select<br /> @spidlow = 0<br /> ,@spidhigh = 32767<br /><br />--------------------------------------------------------------<br />IF (@loginame IS NULL) --Simple default to all LoginNames.<br /> GOTO LABEL_17PARM1EDITED<br /><br />--------<br />-- select @sid1 = suser_sid(@loginame)<br />select @sid1 = null<br />if exists(select * from master.dbo.syslogins where loginname = @loginame)<br />select @sid1 = sid from master.dbo.syslogins where loginname = @loginame<br /><br />IF (@sid1 IS NOT NULL) --Parm is a recognized login name.<br /> begin<br /> select @sidlow = suser_sid(@loginame)<br /> ,@sidhigh = suser_sid(@loginame)<br /> GOTO LABEL_17PARM1EDITED<br /> end<br /><br />--------<br /><br />IF (lower(@loginame) IN ('active')) --Special action, not sleeping.<br /> begin<br /> select @loginame = lower(@loginame)<br /> GOTO LABEL_17PARM1EDITED<br /> end<br /><br />--------<br /><br />IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0) --Is a number.<br /> begin<br /> select<br /> @spidlow = convert(int, @loginame)<br /> ,@spidhigh = convert(int, @loginame)<br /> GOTO LABEL_17PARM1EDITED<br /> end<br /><br />--------<br /><br />RaisError(15007,-1,-1,@loginame)<br />select @retcode = 1<br />GOTO LABEL_86RETURN<br /><br /><br />LABEL_17PARM1EDITED:<br /><br />-------------------- Capture consistent sysprocesses. -------------------<br /><br />if exists (select * from [DBAdmin].[dbo].[sysobjects] where id = object_id(N'[DBAdmin].[dbo].[util_sysprocesses]') /*and OBJECTPROPERTY(id, N'IsUserTable') = 1*/ )<br />truncate table [DBAdmin].[dbo].[util_sysprocesses]<br /><br />else<br /><br />CREATE TABLE [DBAdmin].[dbo].[util_sysprocesses] (<br />[spid] [smallint] NOT NULL ,<br />[status] [nchar] (30) COLLATE Latin1_General_BIN NOT NULL ,<br />[sid] [binary] (86) NOT NULL ,<br />[hostname] [nchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE Latin1_General_BIN NOT NULL ,<br />[program_name] [nchar] (12<img src='/community/emoticons/emotion-11.gif' alt='8)' /> COLLATE Latin1_General_BIN NOT NULL ,<br />[cmd] [nchar] (16) COLLATE Latin1_General_BIN NOT NULL ,<br />[cpu] [int] NOT NULL ,<br />[physical_io] [bigint] NOT NULL ,<br />[blocked] [smallint] NOT NULL ,<br />[dbid] [smallint] NOT NULL ,<br />[loginname] [sysname] NULL ,<br />[spid_sort] [smallint] NOT NULL ,<br />[last_batch_char] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL <br />) ON [PRIMARY]<br /><br />Insert INTO [DBAdmin].[dbo].util_sysprocesses<br />SELECT<br /><br /> spid<br /> ,status<br /> ,sid<br /> ,hostname<br /> ,program_name<br /> ,cmd<br /> ,cpu<br /> ,physical_io<br /> ,blocked<br /> ,dbid<br /> ,convert(sysname, rtrim(loginame))<br /> as loginname<br /><br /> ,spid as 'spid_sort'<br /><br /> , substring( convert(varchar,last_batch,111) ,6 ,5 ) + ' '<br /> + substring( convert(varchar,last_batch,113) ,13 ,8 )<br /> as 'last_batch_char'<br /><br /> from master.dbo.sysprocesses (nolock)<br /><br />order by dbid, loginname, cpu desc<br /><br />--------Screen out any rows?<br /><br />IF (@loginame IN ('active'))<br /> DELETE [DBAdmin].[dbo].util_sysprocesses<br /> where lower(status) = 'sleeping'<br /> and upper(cmd) IN (<br /> 'AWAITING COMMAND'<br /> ,'MIRROR HANDLER'<br /> ,'LAZY WRITER'<br /> ,'CHECKPOINT SLEEP'<br /> ,'RA MANAGER' )<br /><br /> and blocked = 0<br /><br />--------Prepare to dynamically optimize column widths.<br /><br />Select<br /> @charsidlow = convert(varchar(85),@sidlow)<br /> ,@charsidhigh = convert(varchar(85),@sidhigh)<br /> ,@charspidlow = convert(varchar,@spidlow)<br /> ,@charspidhigh = convert(varchar,@spidhigh)<br /><br />SELECT<br /> @charMaxLenLoginName =<br /> convert( varchar<br /> ,isnull( max( datalength(loginname)) ,5) )<br /><br /> ,@charMaxLenDBName =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,db_name(dbid))))) ,15) )<br /><br /> ,@charMaxLenCPUTime =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,cpu)))) ,7) )<br /><br /> ,@charMaxLenDiskIO =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,physical_io)))) ,6) )<br /><br /> ,@charMaxLenCommand =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,cmd)))) ,7) )<br /><br /> ,@charMaxLenHostName =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,hostname)))) ,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> )<br /><br /> ,@charMaxLenProgramName =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,program_name)))) ,11) )<br /><br /> ,@charMaxLenLastBatch =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,last_batch_char)))) ,9) )<br /><br /> ,@charMaxLenStatusL =<br /> convert( varchar<br /> ,isnull( max( datalength( rtrim(convert(varchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' />,status)))) ,10) )<br /><br /> from<br /> [DBAdmin].[dbo].util_sysprocesses<br /> where<br />-- sid &gt;= @sidlow<br />-- and sid &lt;= @sidhigh<br />-- and<br /> spid &gt;= @spidlow<br /> and spid &lt;= @spidhigh<br /><br />--------Output the report.<br /><br />EXECUTE( '<br />SET nocount off<br /><br />SELECT<br /> SPID = convert(char(5),spid)<br /><br /> ,Status =<br /> CASE lower(status)<br /> When ''sleeping'' Then lower(substring(status,1,' + @charMaxLenStatusL + '))<br /> Else upper(substring(status,1,' + @charMaxLenStatusL + '))<br /> END<br /><br /> ,Login = substring(loginname,1,' + @charMaxLenLoginName + ')<br /><br /> ,HostName =<br /> CASE hostname<br /> When Null Then '' .''<br /> When '' '' Then '' .''<br /> Else substring(hostname,1,' + @charMaxLenHostName + ')<br /> END<br /><br /> ,BlkBy =<br /> CASE isnull(convert(char(5),blocked),''0'')<br /> When ''0'' Then '' .''<br /> Else isnull(convert(char(5),blocked),''0'')<br /> END<br /><br /> ,DBName = substring(case when dbid = 0 then null when dbid &lt;&gt; 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')<br /> ,Command = substring(cmd,1,' + @charMaxLenCommand + ')<br /><br /> ,CPUTime = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')<br /> ,DiskIO = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')<br /><br /> ,LastBatch = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')<br /><br /> ,ProgramName = substring(program_name,1,' + @charMaxLenProgramName + ')<br /> ,SPID = convert(char(5),spid) --Handy extra for right-scrolling users.<br /> from<br /> [DBAdmin].[dbo].util_sysprocesses --Usually DB qualification is needed in exec().<br /> where<br /><br /> spid &gt;= ' + @charspidlow + '<br /> and spid &lt;= ' + @charspidhigh + '<br /><br /> -- (Seems always auto sorted.) order by spid_sort<br />ORDER BY DBName <br /><br />SET nocount on')<br /><br />/*****AKUNDONE: removed from where-clause in above EXEC sqlstr<br /> sid &gt;= ' + @charsidlow + '<br /> and sid &lt;= ' + @charsidhigh + '<br /> and<br />**************/<br /><br /><br />LABEL_86RETURN:<br /><br /><br />if (object_id('[DBAdmin].[dbo].util_sysprocesses') is not null)<br /> truncate table [DBAdmin].[dbo].util_sysprocesses<br /><br />return @retcode<br />GO<br />
  2. satya Moderator

    A good one to get the information in the above format, if one got bored with usual SP_WHO2 format.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page