exec sp_who2 'active' - Status = Sleeping?

OK... I'm getting confused...
I'm executing:
exec sp_who2 'active'
And getting the below results:
SPID Status Login
-- -- --
198 sleeping Domain\User
...
...
With that said, can someone please refresh my memory about sleeping SPID's
status? How can it be active but sleeping?
Thanks,
MichaelI ask because when I query sysprocesses, I see CPU and physical I/O increase
while it's sleeping...
SYSPROCESSES ServerName 134219768
**********************************
spid cpu physical_io
-- -- --
69 188 135499
69 79 135499
69 141 135499
69 172 135499
69 218 135499
69 187 135499
69 171 135499
69 172 135499
Thanks!|||Hi Batman
There is no value 'active'.
Take a look at the code for sp_who2. You'll see that if you specify
'active', it eliminates spids where status='sleeping' and command is one of
a small list of values, including 'AWAITING COMMAND', and there is nothing
blocking. So a spid can be considered active if it is currently waiting for
some resource. What does the COMMAND column report?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"BATMAN" <BATMAN@.discussions.microsoft.com> wrote in message
news:073AFE2C-C957-4978-8AFF-420C5C7A6D86@.microsoft.com...
> OK... I'm getting confused...
> I'm executing:
> exec sp_who2 'active'
> And getting the below results:
> SPID Status Login
> -- -- --
> 198 sleeping Domain\User
> ...
> ...
> With that said, can someone please refresh my memory about sleeping SPID's
> status? How can it be active but sleeping?
> Thanks,
> Michael|||The COMMAND column says "SELECT"...
Not to mention, we use the sp_blocker_pss80 sproc and is shows the same
status and command.
Again it shows sleeping but the cumulative CPU and I/O grew over a minute
and then disapeared...|||A process can sleep occasionally, but still be an active process, and use
system resources.
Perhaps what you mean by 'active' and what sp_who2 means by active are not
the same thing.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Batman" <Batman@.discussions.microsoft.com> wrote in message
news:B63A566F-E9B6-4754-90EB-E2B2DD17D938@.microsoft.com...
> The COMMAND column says "SELECT"...
> Not to mention, we use the sp_blocker_pss80 sproc and is shows the same
> status and command.
> Again it shows sleeping but the cumulative CPU and I/O grew over a minute
> and then disapeared...
>
>
>