sp_who2 output

[EJO]

Thanks, folks, for taking time to help!

In query analyzer and profiler, there seem to be system processes that
are generating high cpu time and disk io. Well, I guess the short of
it is that I am having a problem interpreting what I am seeing so that
I can take the next step in corrective action. There are also several
domain controlled logins that appear to be on the same host name, at
the same time--these are laptops, not terminal servers--why would the
system be reporting such a thing?

Here is a sample of the output from sp_who2:

SPID STATUS LOGIN HOSTNAME BLKBY DBNAME COMMAND CPU TIME DISK
IO LASTBATCH PROGRAMNAME
1 BACKGROUND sa . . NULL LAZY WRITER
750 0 6/3/2005 5:14
2 sleeping sa . . NULL LOG WRITER
10250 0 6/3/2005 5:14
3 BACKGROUND sa . . master SIGNAL HANDLER
16 0 6/3/2005 5:14
4 BACKGROUND sa . . NULL LOCK MONITOR
203 0 6/3/2005 5:14
5 BACKGROUND sa . . master TASK MANAGER
0 151 6/3/2005 5:14
7 sleeping sa . . NULL CHECKPOINT
SLEEP 2438 2239 6/3/2005 5:14
10 BACKGROUND sa . . master TASK MANAGER
0 25 6/3/2005 5:14
11 BACKGROUND sa . . master TASK MANAGER
0 163 6/3/2005 5:14
12 BACKGROUND sa . . master TASK MANAGER
0 70 6/3/2005 5:14
13 BACKGROUND sa . . master TASK MANAGER
0 155 6/3/2005 5:14
51 sleeping sa MyServer . msdb AWAITING
COMMAND 68337 138 6/8/2005 13:00 SQLAgent - Generic Refresher
52 sleeping sa MyServer . msdb AWAITING
COMMAND 17462 6 6/8/2005 13:00 SQLAgent - Alert Engine
53 sleeping DomainUser1 LT1 . VPN
Master AWAITING COMMAND 1687 54 6/8/2005 12:31 Crystal Reports

54 sleeping sa MyServer . VPN
Master AWAITING COMMAND 377 29 6/8/2005 10:49 MS SQLEM

55 RUNNABLE DomainUser2 MyServer .
master SELECT 860 0 6/8/2005 12:40 SQL Profiler

56 RUNNABLE DomainUser2 LT2 . VPN SELECT
344 9 6/8/2005 12:50 Microsoft® Access
57 sleeping DomainUser3 LT2 . VPN
Master AWAITING COMMAND 110 29 6/8/2005 12:52 Microsoft® Access

58 sleeping DomainUser1 LT1 .
master AWAITING COMMAND 1172 15 6/8/2005 9:31 Crystal Reports

59 sleeping DomainUser4 LT2 . VPN
Master AWAITING COMMAND 15 1 6/8/2005 12:50 Microsoft® Access

62 sleeping DomainUser5 LT2 . VPN
Master AWAITING COMMAND 32 2 6/8/2005 12:52 Microsoft® Access

63 sleeping DomainUser6 LT2 . VPN
Master AWAITING COMMAND 501 0 6/8/2005 12:52 Microsoft® Access

64 sleeping DomainUser6 LT2 . VPN AWAITING
COMMAND 1016 0 6/8/2005 12:52 Microsoft® Access
65 sleeping DomainUser6 LT2 . VPN
Master AWAITING COMMAND 47 49 6/8/2005 12:52 Microsoft® Access

66 sleeping DomainUser3 LT2 . VPN AWAITING
COMMAND 203 0 6/8/2005 12:50 Microsoft® Access
67 sleeping DomainUser6 LT2 . VPN AWAITING
COMMAND 249 0 6/8/2005 12:52 Microsoft® Access
68 RUNNABLE sa MyServer . master SELECT
INTO 62 12 6/8/2005 12:59 SQL Query Analyzer

Thanks,

Eric

[Erland Sommarskog]

Re: sp_who2 output

EJO (5p1der@century tel.net) writes:[color=blue]
> In query analyzer and profiler, there seem to be system processes that
> are generating high cpu time and disk io.[/color]

They don't look particularly high to me. CPUTime is in milliseconds, so
10 seconds in five days is not a lot. And 2400 disk IO for the checkpoint
process looks like a calm system to me...
[color=blue]
> There are also several domain controlled logins that appear to be on the
> same host name, at the same time--these are laptops, not terminal
> servers--why would the system be reporting such a thing?[/color]

An application can set the host name when the connection, and can
blatantly like if it wishes to.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[EJO]

Re: sp_who2 output

Thanks for the assistance. Since that is about what I get with each
sp_who2, I thought I always had excessive times--which you are
indicating it is not. I guess I'll be looking else where for my
performance issues...as I still need to finish going through the
profiler output.

Once again, thanks for your assistance.

Eric