SQL Server System Session ID是不是1-50？

今天在論壇看到一篇文章問到為什麼SQL Server系統的SESSION ID是從1到50的，以前看過文章，這些Session是SQL Server為了運行系統活動比如（(lazy writer, ghost record cleanup, DTC commit/abort），所以會保留50 Session ID給SQL Server使用,使用者會話從51開始。

 

在2005之前查詢使用者會話使用：

 

select *
from sysprocesses
wherespid < 50

 

但是在SQL Server 2005之後已經沒有這個限制了，我在MSDN上找到了下面這篇文章：How It Works: System Sessions

 

Looking at a SQL Server error log it is formatted with the date,
time and session identifier.   Many of the identifiers contain the
s following the spid value.

2008-01-08 20:03:36.12 spid5s

The s indicates that the session is a system session.   Prior to SQL Server 2005 all system sessions were limited to session ids less than 50.  SQL Server 2005 lifted that restriction.   In order identify a session performing system actives
(lazy writer, ghost record cleanup, DTC commit/abort, ...) the sessions are identified as system sessions.

Instead of the older "select * from sysprocesses where spid < 50" you should use "select * from sys.dm_exec_sessions where is_user_process = 0" to identify system processes.

 

根據上面的講法系統Session ID有可能超過50，所以查詢使用者Session ID使用

 

select *
from sys.dm_exec_sessionswhereis_user_process= 1

 

is_user_process

bit

如果會話是系統會話，則為 0。否則，為 1。不可為空白值

 

更多資訊參考：http://msdn.microsoft.com/zh-cn/library/ms176013(v=sql.90).aspx