differences between SPID and session_id

I'm configuring a job to get the SPID of an offending process and kill it. I'm using a mix between the great sp_Whoiscative from @AdamMachanic and joining with sysprocesses on login name and SPID and session_id values as those are supposed to be the same (or I'm wrong and then that's my error?).

I'm getting something I don't understand: for a same SPID from sysprocesses I'm getting different session_id values from whoisactive results. What is incorrect here?

Here is the code I'm using. The filter to get values 10s before current datetime is because the whoisactive is running with the parameter @deltainterval that takes about 10s to execute on each run.

USE master;

--  Log information about current running processes to table Log_WhoIsActive
    EXEC [master].[dbo].[sp_WhoIsActive]
        @get_full_inner_text = 1,
        @get_plans = 2,
        @get_outer_command = 1,
        @get_transaction_info = 1,
        @get_task_info = 2,
        @get_locks = 1,
        @get_avg_time = 1,
        @get_additional_info = 1,
        @delta_interval = 2,
        @sort_order = '[start_time] DESC',
        @destination_table = 'Log_WhoIsActive_tests';

--compare date from sysprocesses with data gathered on sp_whosiactive
SELECT  dt.collection_time, sp.spid, dt.[dd hh:mm:ss.mss], sp.loginame,
        dt.login_name, dt.session_id
FROM    sys.sysprocesses sp JOIN Log_WhoIsActive_tests dt
            ON sp.loginame = dt.login_name
WHERE   sp.loginame = 'my_login_name'
        AND sp.status = 'runnable'
        AND sp.spid > 50
        AND dt.collection_time > DATEADD(ss, -10, GETDATE());

SPID === session_id.

What you are seeing is a Cartesian product since you are joining on a field that is not unique within the data set. You currently have:

FROM    sys.sysprocesses sp JOIN Log_WhoIsActive_tests dt
            ON sp.loginame = dt.login_name

You should probably, at the very least, add the following condition to that JOIN:

AND sp.spid = dt.session_id

Also, please do not use sysprocesses. It was deprecated when SQL Server 2005 was released, and the sys.sysprocesses "table" you are using is just a compatibility view. The MSDN page for sys.sysprocesses even has a note at the top stating:


This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

You should instead use the new DMV (Dynamic Management Views):

• sys.dm_exec_sessions

• sys.dm_exec_requests

• sys.dm_exec_connections

Please note that a Login might need to be granted the server-level permission of VIEW SERVER STATE in order to see more than their own data for sys.dm_exec_sessions and sys.dm_exec_requests, or see anything at all in sys.dm_exec_connections.

If this permission is needed, then the current database when you run the GRANT statement needs to be master as this is a server-level permission.

StackExchange Database Administrators Q#145032, answer score: 6