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 … Continue reading SQL Server SPID – What is it?