Sysprocesses

Question

Post reply

Sysprocesses

Mark Shvarts 1

SSCertifiable

Points: 5702
More actions
December 5, 2006 at 7:57 am

#175171

Hi,
Where can I find good, detail explanation or a tutorial about all fields in sysprocesses table, especially WaitType and LastWaitType? B.O.L is very poor about that.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Rich Hansell SSCrazy Points: 2346 More actions · Answer 1

This might help.. http://support.microsoft.com/kb/822101

Thanks,

Rich

Mark Shvarts 1 SSCertifiable Points: 5702 More actions · Answer 2

That's good article, but they don't answer to my question what type of wait is CXPACKET.

Rich Hansell SSCrazy Points: 2346 More actions · Answer 3

This could be your answer then.. http://www.sqldev.net/misc/waittypes.htm

Thanks,

Rich

MohammedU SSCoach Points: 19644 More actions · Answer 4

It is related to parallalism...

Read the following threads...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=183243

http://www.sqlblogs.com/top/ng/group~2/~138495~__CXPACKET-and-max-degree-of-parallelism/index.aspx

http://support.microsoft.com/kb/293232

MohammedU
Microsoft SQL Server MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

In 2005 it's better to use the dm views sys.dm_exec_sessions and sys.dm_exec_requests than sysprocesses. They're a lot better documented for starts

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mojo-168709 SSCommitted Points: 1825 More actions · Answer 6

I'm still coming back to sysprocesses, for only one reason: it's easy to view which database a spid is connecting to.

But I couldn't find a way to get this info with _sessions and _connections dmv's. The _requests dmv doesn't seem to return any inactive connections, hence, the database_id it returns is not complete.

Is there any other way to get this info using dmv's?