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]

9 comments
Mahendra 09 Jun 2011 at 3:04 am

Going by the line “Temp tables that are created within a SPID are accessible from any proceeding execution in that SPID”,
1. Create a stored procedure that creates a #temp table.
2. Open a new Query Window in SSMS.
3. Execute the stored procedure and then from outside the stored procedure try to excute the following statement “Select * From #temp” it will not get executed.

My question is when the @@SPID is same why the temp table created in a stored procedure is not accessible outside the stored procedure.

Anonymous 29 Sep 2011 at 3:45 pm

The New Query window is a separate connection and therefore spid.

Anonymous 07 May 2012 at 5:35 am

New query window was opened to execute procedure and run select statment. Both in the same query window. Both on same spid.

juan 08 Aug 2014 at 8:54 pm

It appears that way but in the background, it creates a new SPID to execute the sproc. you can change your temp table from #temp to ##temp and it will be accessible outside that SPID.

AGuy 13 Oct 2015 at 9:08 pm

Executing a stored procedure absolutely does NOT create a new SPID. If you open a query window and execute 20 different stored procedures, they will ALL be ran under that same SPID (assuming you don’t do something extreme like use OpenQuery()). A SPID can, however run under one or more processes due to degree of parallelism.

To answer the original question…Reread the statement “Temp tables that are created within a SPID are accessible from any PROCEEDING execution in that SPID”.

You can create a stored procedure that populates (not creates) a temp table that is expected to be created in the same session prior to the proc executing, then open a new query window, create a temp table (same name that the proc uses), execute the proc and select from the temp table.

Err: Changes to the state or options of database ‘databasename’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. « R. Suharta's Blog 26 May 2011 at 10:04 pm

[…] is you need to kill the sql session which is locking the database. Firstly you need to query which spid is locking the […]

Err: Changes to the state or options of database ‘databasename’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it. « R. Suharta's Blog 26 May 2011 at 10:02 pm

[…] is you need to kill the sql session which is locking the database. Firstly you need to query which spid which is locking the […]

Merete Wrangell 23 May 2011 at 1:17 am

Have problems with spid -2 in sqlserver 2005. How do I kill these processes?

Tanuj Bhargava 11 May 2011 at 6:10 am

Thanks a lot for the accurate information.

Featured Articles

 Site Author

  • Thanks for visiting!
css.php