SQL Server: Different Ways to find SPID


There are many ways to know the Server Process id or now also known as Session ID (SPID). The most common methods are as follows:

1. Use global variable @@SPID

SELECT @@SPID as spid
sql-global-ssid

2. Use Properties window

While working in the Query Analyzer, press F4. A Property Window opens and you can see SPID in the last row

SQL Server SPID

3. Use System procedure sp_who

EXEC sp_who

sql-spwho

The above will list out all the processes with spid. The last spid is the one related
to your session.

4. Use sysprocesses view

select spid from sys.sysprocesses

This query will list out all the processes with spid. The last spid is the one related
to your session.


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

3 comments:

James said...

I got this for your 4th method

SELECT @@SPID
Gets 65

Then the

select spid from sys.sysprocesses

The last was 78 :)

Suprotim Agarwal said...

Looks like it is the second last row that has the SSID. I am not sure though.

Madhivanan, any inputs?

Madhivanan said...

I think it may not gaurantee to give the id in the order we expect. sysprocesses will have the spid for the current session