Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

In SQL Server, each session has its own spid. Spids are unique at any given notice, but spids, like process and thread identifiers in the OS are recycled.

However sys.dm_exec_sessions has other columns with session metadata. Is there a combination of columns that is guaranteed to be unique for a server instance?

share|improve this question
Are you talking about being able to tell two spids apart if you are taking snapshots of sys.dm_exec_sessions over time? – Aaron Bertrand♦ Jan 11 at 20:03
2  
I don't think there are any combination of columns that can guarantee uniqueness for the lifetime of the server. For example, when the clocks get set back an hour (for a server in a time zone that practices daylight savings) and 2 sessions start exactly an hour apart and get the same session ID, that would not be unique. – Robert L Davis Jan 11 at 20:35
@AaronBertrand well I'm trying to see if a spid still exists. – Justin Dearing Jan 11 at 20:42
@RobertLDavis I'm really ok with edge case false positives for my purposes. – Justin Dearing Jan 11 at 20:43
In that case, you are probably okay with those columns. I only called it out because you asked for a guarantee. – Robert L Davis Jan 11 at 21:30

2 Answers

up vote 3 down vote accepted

Sessions belong to connections (1:M) and connections have unique GUID identifier, see sys.dm_exec_connections:

connection_id uniqueidentifier Identifies each connection uniquely. Is not nullable.

Whenever you capture sys.dm_exec_sessions, join with sys.dm_exec_connections and capture the connection_id too.

share|improve this answer
up vote 0 down vote

You can associate a spid with its start time via the login_time column. Other than a spid being recycled at a rate faster than the resolution of login_time, this is unique per server instance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.