SQLTeam.com | Weblogs | Forums

How often can SPID # be reused for different sessions? Does it really happen more often than we think?

I have noticed examining the logged active processes (I save resultset of sp_whoisactive to table every 30 seconds) that the same spid in just one hour shows 30 rows. In some cases it can be explained by Connection Pooling running similar queries using same Session with same spid, same login time for all rows) but in some cases they are different sessions, different login times, different capture time, suggesting same SPID reuse by SQL Engine for different Session.
About 1000 active processes get logged each hour. How realistic it is that I would see several SPIDs each used dozen of times for different queries during just one hour as being totally random number being reused? I understand that Session_ID reuse is allowed. But can it realistically be happening so often?

Yes. Why wouldn't it? Connections have some overhead so it's worth it to keep the number small enough to handle the demand, but no more.

I only have 4 teaspoons at home. I use 1 to put sugar in my coffee and stir it, I use a 2nd for eating whatever I have for breakfast. Every now and then I'll have ice cream, or soup, or need to stir something on the stove; I'll reuse one of those spoons, with a rinse in-between. Now if I worked in a restaurant or industrial kitchen, obviously we'll need a lot more spoons.

There's nothing magical about a session_id, it's just a pointer to a connection state, and SQL Server only creates a new one when it doesn't have any freely available. 1000 processes/hour is not particularly busy for a SQL Server.