SPID which stands for Server Process ID is essentially a session or a connection in SQL Server. Every time when the 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.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.
To find the SPID for your current execution window run this.
SELECT @@SPID
In order to view all the connections in SQL Server execute the following query.
SELECT *
FROM sys.dm_exec_sessions
From here we see a session_id shown in the left hand column. This is also known as the SPID.
Some of the properties of SPIDs are shown below
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.
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. You can run sp_who2 stored procedure to find blocking.
An SPID can be in any of the following statuses
- Running : means the session is already running, in other words the application has submitted a query to sql server for processing.
- Suspended: means the session is not currently active, it could be waiting for an IO or other resources ; for example if sql is processing to return the entire data from a table, the task will be suspended till it gets the entire data from the table and once it gets the data the process will move to the ‘Runnable’ queue.Following query shows the suspended processes
1: SELECT wt.session_id, ot.task_state, wt.wait_type, wt.wait_duration_ms, wt.blocking_session_id, wt.resource_description, es.[host_name], es.[program_name]
2: FROM sys.dm_os_waiting_tasks wt
3: INNER JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address
4: INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id
5: WHERE es.is_user_process = 1
3. Runnable : Means the process is waiting for the CPU.The RUNNABLE queue is like a a grocery analogy where there are multiple check out lines. The register clerk is the CPU. There is just one customer checking out or “RUNNING” at/ by any given register. The time spent in the checkout line represents CPU pressure. So this SPID is waiting for that customer who is running (with register clerk) to get out so that it can start RUNNING
4. Pending : the request is ready to run but waiting for a worker thread to pick it up.
5. Background :The request is a background thread such as Resource Monitor or Deadlock Monitor.
6. Sleeping : situation where the SQL Server is waiting for the next command from the client.
7.Dormant: SQL Server is resetting the session
8.Rollback : The session has a transaction rollback in process
