SPID numbers

[SQLBill]

Where can I find a listing of the System Process ID (SPID) numbers and what they equate to? I know some of them are assigned to users and do not have a set definition. But some of them do apparently have set definitions. I have been able to find two of them SPID 2 = Lock Monitor and SPID 3 = Lazy Writer (whatever that is). Can anyone point me to where I can find a listing of the SPIDs that have a set meaning?

-Bill

 

[SQLBill]

Forgot to add....

I tried the BOL, but didn't find anything there. In this forum, I tried keyword searches (SPID, System Process ID, Process ID), and I looked through the FAQ. But I didn't have any success.

-Bill

 

[tlbroadbent]

Generally, system (or background) processes run in SPIDs numbered less than 10. Search BOL for "lazywriter", "logwriter", etc. for details about these processes.

Try the following link for some more info about system and background processes.

http://msdn.microsoft.com/library/en-us/adminsql/ad_perfmon_6ofn.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SQLBill]

Thanks Terry. I've been to the link you provided and couldn't find what I was looking for, but I'll keep on trying. Likewise, I'm still trying BOL for answers. Searches on SPID or 'system process id' just don't work.

-Bill

 

[tlbroadbent]

Did you search for lazywriter or logwriter in BOL? Specifically check this topic - "Freeing and Writing Buffer Pages". See the online version at

http://msdn.microsoft.com/library/en-us/architec/8_ar_sa_8unn.asp

You should also read about locks and to understand that the Lock Monitor runs to detect locks and deadlocks. It runs periodically as do the lazywriter and logwriter. Note also that these system processes are critical to SQL Server and cannot be eliminated. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[SQLBill]

Terry, again thanks for your help. I didn't start searching for things like 'lock monitor', 'lazy writer', etc. My original need was to find a listing of the system SPIDs so I would know which ones I couldn't/shouldn't KILL. By watching my current activity, I have been able to come up with my own list and am working on searching the BOL for the commands related to those SPIDs (as you suggested).

By the way....I have found several of your previous postings helpful (I try to mark them so). Especially, the ones where you have provided a very valuable listing of SQL Server related links. Thank you.

One of these days I may actually know what I'm doing and it will be thanks to people like you who willingly pass their knowledge onto people like me.

-Bill