SQL Server SPID – What is it?

A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, or Session.

Viewing SPIDs

In order to view all the connections in SQL Server execute the following query.
[cc lang=”sql”]
SELECT *
FROM sys.dm_exec_sessions
[/cc]

From here we see a session_id shown in the left hand column. This is also known as the SPID.

To find the SPID for your current execution window run this.
[cc lang=”sql”]
SELECT @@SPID
[/cc]

Connection Settings

Each SPID can have it’s own connection settings. Connection settings can be defined by the connection string or the default values for the SQL Server instance, so if two SPIDs come in through the same connection string, then they will also have the same connection settings. This is important because execution plans that are generated for queries are dependent on the connection settings. If two different SPIDs come in with different connection settings, then they will not use the same execution plan defined for a SQL statement. In order to view the connections settings execute the SQL Statement shown above.

Transaction Isolation

SPIDs can have their own transaction isolation levels defined globally. When set within a connection, all proceeding executions maintain the same transaction isolation. For more on isolation levels, view this

Temp Table sharing

Temp tables that are created within a SPID are accessible from any proceeding execution in that SPID. This is how separate stored procedures can share the temp table because any temp table created in a spid’s session is global to the session. This comes in useful when sharing data between stored procedures.

Blocking

Because SPIDs define an atomic operation and are independent, they can often compete with each other depending on their Transaction Isolation levels, the objects they are accessing, and the operation they are performing. The quickest way to find blocking is to use the sp_who2 procedure.

SPID Status

Because a SPID is defined as a connection, it is not always running (or executing). In order to find the status of the SPIDs, execute the following:
[cc lang=”sql”]

SELECT
SPID = er.session_id
,Status = ses.status
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
[/cc]