1

There are an application and SQL Server which communicate with each other. A user runs two instances of the application in the computer. He starts execution of one operation in the first instance, and then starts execution of another operation in the second one. In this case, is it possible that SQL Server uses the same spid for these two queries? And is it possible at all?

I have read this question (What is the relationship between open SqlConnections in the client app and processes in SQL Server?). If I correctly understand this situation can occur if pooling = true, because SQL Server can choose existing connection of the first instance for second one. Is this correct?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Slava Podolskiy
  • 155
  • 1
  • 11
  • 1
    *Connection pooling* means that a connection that is **no longer being used** is put back into a pool and then reused later on - but that **ONLY** happens when the connection is **no longer needed**, e.g. after has been **closed** by the previous user. A connection that is **in use with a user** will ***NEVER*** be "pooled" and used for a second user at the same time! – marc_s Jan 31 '17 at 13:17
  • @marc_s Ok, thanks for answer! Tell me please, whether following case is correct. If a user closes an application which uses the first connection then the connection is put back into a pool. After a user runs the application again and the first connection will be used (with the same spid and the same login_time parameters)? – Slava Podolskiy Jan 31 '17 at 13:44
  • No, I believe each connection gets a brand new, fresh spid and login_time – marc_s Jan 31 '17 at 13:49
  • @marc_s Thank you a lot! – Slava Podolskiy Jan 31 '17 at 13:50
  • 3
    This is one of those "why are you asking this" questions. Be careful to never rely on a spid being "unique" for any length of time, and be wary of things like MARS, where more than one thing can be happening on a single connection (but still from the same application). You can be sure that at any single moment in time, one spid belongs to one connection opened by one application. But that same spid might belong to a completely different application 2 seconds later, so be sure to trace all connection open/close events correctly. – Jeroen Mostert Jan 31 '17 at 14:18
  • @marc_s: I want to explore the "then the connection is put back into a pool" and the assertion that it gets a new spid when the connection is pulled from the pool. I don't believe this is the case. The point of connection pooling is to avoid the overhead of establishing a connection. If a new spid is generated, that implies that the connection has been dropped. – Ben Thul Jan 31 '17 at 16:14

0 Answers0