sp_who2 output

Question

Post reply

sp_who2 output

Scott Skeen

SSC Veteran

Points: 227
More actions
April 5, 2005 at 7:04 am

#85781

Hey gang, new poster with a problem!!!
When I run sp_who2 I get the expected info back but,
it is not in order by SPID, I have only noticed this behavior recently, I am almost certain these were all ordered by SPID at some point.
Do I need more caffeine, or have I possibly changed a QA setting that is throwing things out of whack?
either of these is a possibility.
Thanks in advance for any help.
Scott

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ryan-209402 Hall of Fame Points: 3707 More actions · Answer 1

You could also do something like:

SELECT * FROM master.dbo.sysprocesses

This won't give you everything that sp_who2 gives you but might give you what you need. However, when I run sp_who2 I get the results ordered by spid so I think you might need some more caffeine.

Scott Skeen SSC Veteran Points: 227 More actions · Answer 2

CAFFEINE!!!! YAY!!!!

I do appreciate the suggestion and your suggestion would work, I am more curious as to what may have changed to cause this. sp_who still reports in ASC SPID order I am curious why sp_who2 has suddenly forgotten how to count in order, my overriding concern is that this may affect other result sets as well.

philcart SSC-Forever Points: 47794 More actions · Answer 3

Interestingly the sp_who2 code contains this comment in the dynamic SQL,

-- (Seems always auto sorted.) order by spid_sort

What collation are you using? The spid field is converted to a char(5) so that might have an effect??

You could always capture the output and produce your own sp_who report

http://www.sqlserver.org.au/resources/ViewResource.aspx?resourceId=8

--------------------
Colt 45 - the original point and click interface

Scott Skeen SSC Veteran Points: 227 More actions · Answer 4

Phil-

I am using SQL_Latin1_General_Cp1_CI_AS as the collation for the server. Again it may be a caffeine issue...

Thanks for the link too.

If I find out anything else interesting I will post a follow-up here.

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

The easiest thing to do here is go to the sp_who2 proc (in master database) and alter it by uncommenting the order by in the dynamic SQL.

Signature is NULL

hoo-t SSChampion Points: 12713 More actions · Answer 6

I was taught to NEVER, EVER, EVER change a system stored proc, so I created a wrapper for sp_who2 to display the results in the order I wanted (order of cpu usage). (By the way, I think Calvin's suggestion is perfectly valid, and won't really affect much if the fears of those who teach the never ever rule are realized. Basically, any upgrade, service pack, security patch... that you apply may overwrite your change, but really, that could happen with my scenario as well.) Anyway, here's my wrapper, and you could easily modify it to suit your needs, even create multiple copies to output various ways....

create procedure sp_who2a

as

/*************************************************************/

--

-- Module Name: sp_who2a

--

-- Description:

--

-- This procedure is "front end" to sp_who2 which provides

-- logins in order of cpu usage. also gives counts of

-- current logins/connections.

--

-- Written By: Steve Phelps

--

-- Date: November 10, 2003

--

-- Modified :

-- Date:

--

-- USAGE:

--

-- exec sp_who2a

--

/*************************************************************/

declare

@count int

create table #who2

(

#SPID int NULL,

#Status varchar(30) NULL,

#Login sysname NULL,

#HostName sysname NULL,

#BlkBy varchar(128) NULL,

#DBName sysname NULL,

#Command varchar(128) NULL,

#CPUTime int NULL,

#DiskIO int NULL,

#LastBatch char(14) NULL,

#ProgramName sysname NULL,

#SPIDb int NULL)

create table #who2_counts

(

#Login sysname NULL,

#Count int)

insert #who2

exec sp_who2

select

#SPID as SPID,

#Status as Status,

#Login as Login,

#HostName as HostName,

#BlkBy as BlkBy,

#DBName as DBName,

#Command as Command,

#CPUTime as CPUTime,

#DiskIO as DiskIO,

#LastBatch as LastBatch,

#ProgramName as ProgramName,

#SPIDb as SPID

from #who2

order by #CPUTime desc, #Login, #HostName

insert #who2_counts

select distinct

#Login as Login,

count(#Login) as [Count]

from #who2

group by #Login

order by [Count] desc

select @count = count(*)

from #who2

insert #who2_counts

(#Login, #Count)

Values

('*Total*', @count)

select

#Login as Login,

#Count as [Count]

from #who2_counts

order by [Count] desc

drop table #who2

drop table #who2_counts

Scott Skeen SSC Veteran Points: 227 More actions · Answer 7

Wow! Handy little piece of code you got yourself there...can't wait to plug it in and play with it.

Thanks for everyone's help...

I still think I need more caffeine...

hoo-t SSChampion Points: 12713 More actions · Answer 8

Have fun!

By the way, I got my coffee cup ALWAYS within reach!

Another thought just occurred to me regarding the service pack, etc overwriting changes to system procs. It would probably also overwrite whatever changed your sp_who2's behavior and fix it. So you would never even realize that your sp_who2 had been changed back to the MS version!

How's that for a caffeine withdrawal inducer?????

Steve

Calvin Lawson SSChampion Points: 11030 More actions · Answer 9

"It would probably also overwrite whatever changed your sp_who2's behavior and fix it"

Maybe time for sp_who3?

PS: What ever happened to sp_who0 or sp_who1?

On caffeine...what I really like for energy is red-bull and vodka on ice. The working man's speedball, if you will. Of course, during working hours I stick to plain old coffee, but for late night hacks on my own systems (after backing stuff up, of course), nothing like a little caffeine and alchohol to get the rusty gears running.

Signature is NULL

hoo-t SSChampion Points: 12713 More actions · Answer 10

with a parameter to allow sorting in whatever order you want?????

Steve

Paul Brewer SSCrazy Points: 2955 More actions · Answer 11

Adding

DBCC INPUTBUFFER(spid)

to the output table will further enhances the information.

Regards