关于spid

[valid25f]

sql server 2008无论用哪个登录名登陆，我select @@SPID的结果都是54,并且用跟踪看到，即使用别的登录名登陆，查询我自己的某个表依然是显示登录名为sa,spid栏为54,难道是因为表的所有者是用户dbo,而dbo默认的登录名为sa?
每次用客户端连接时，会出现很多spid为74，79的，sql语句是
SELECT se.is_admin_endpoint AS N'AdminConnection',
(SELECT CAST(cfg.value AS bit) AS [ConfigValue]
FROM
sys.configurations AS cfg WHERE configuration_id=16387)
AS N'SmoEnabled'
FROM sys.endpoints se
INNER JOIN sys.dm_exec_connections dmc ON dmc.endpoint_id = se.endpoint_id
WHERE
dmc.session_id = @@spid
这种客户端自己执行的语句。为什么我登陆一次会有两个spid,spid不是一个会话的id吗？难道查用户表时创建一个54逻辑连接，客户端自己查系统表时执行一个74连接？那多次连接后为什么还是54

而如果我用jdbc连接的话，我用哪个登录名，跟踪表就显示哪个登录名，spid都是73，我程序connection打开关闭至少两次，73还是没变。

能否从spid辨别出一个用户的新的会话？

[以学习为目的]

在 SQL Server 中，一个 spid 代表一个会话.

[發糞塗牆]

你每次select @@spid都一样，是因为你在SQLServerManagementstudio（简称SSMS）中的那个执行语句的页面其实spid就是54，你打开新页面再执行，肯定不一样。普及一下，spid<=50的是系统的会话，所以平时查询，最好加>50。
下面语句希望有用：2000不适用
1、查询当前进程及其语句：

-- Current processes and their SQL statements 
 SELECT PRO.loginame AS LoginName 
       ,DB.name AS DatabaseName 
       ,PRO.[status] as ProcessStatus 
       ,PRO.cmd AS Command 
       ,PRO.last_batch AS LastBatch 
       ,PRO.cpu AS Cpu 
       ,PRO.physical_io AS PhysicalIo 
       ,SES.row_count AS [RowCount] 
       ,STM.[text] AS SQLStatement 
 FROM sys.sysprocesses AS PRO 
      INNER JOIN sys.databases AS DB 
          ON PRO.dbid = DB.database_id 
      INNER JOIN sys.dm_exec_sessions AS SES 
         ON PRO.spid = SES.session_id 
      CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM      
 WHERE PRO.spid >= 50  -- Exclude system processes 
 ORDER BY PRO.physical_io DESC 
         ,PRO.cpu DESC;

2、当前连接信息:

-- Connectivity informations 
 ;WITH con AS 
    (SELECT SES.host_name AS HostName 
           ,CON.client_net_address AS ClientAddress 
           ,SES.login_name AS LoginName 
           ,SES.program_name AS ProgramName 
           ,EP.name AS ConnectionTyp 
           ,CON.net_transport AS NetTransport 
           ,CON.protocol_type AS ProtocolType 
           ,CONVERT(VARBINARY(9), CON.protocol_version) AS TDSVersionHex 
           ,SES.client_interface_name AS ClientInterface 
           ,CON.encrypt_option AS IsEncryted 
           ,CON.auth_scheme AS Auth 
     FROM sys.dm_exec_connections AS CON 
          LEFT JOIN sys.endpoints AS EP 
              ON CON.endpoint_id = EP.endpoint_id 
          INNER JOIN sys.dm_exec_sessions as SES 
              ON CON.session_id = SES.session_id) 
 -- Detailed list 
 SELECT * 
 FROM con 
 -- Optional filter 
 --WHERE con.ClientInterface = 'ODBC' 
 ORDER by con.TDSVersionHex,con.HostName 
         ,con.LoginName 
         ,con.ProgramName; 
  
 /* 
 -- Count of different connectivity parameters 
 SELECT COUNT(*) AS [Connections #] 
       ,COUNT(DISTINCT con.HostName) AS [Hosts #] 
       ,COUNT(DISTINCT con.LoginName) AS [Logins #] 
       ,COUNT(DISTINCT con.ProgramName) AS [Programs #] 
       ,COUNT(DISTINCT con.NetTransport) AS [NetTransport #] 
       ,COUNT(DISTINCT con.TDSVersionHex) AS [TdsVersions #] 
       ,COUNT(DISTINCT con.ClientInterface) AS [ClientInterfaces #] 
 FROM con; 
 */