1

On SQL Server 2012 can a process SPID change mid-operation? We have a very unique query that we've been monitoring (production, but controlled testing) and have seen it at different intervals with different SPIDs. It's a unique query that no one else should be running, at least with these parameters, and as such we shouldn't see it with a different SPID, unless... can a query can change SPIDs mid process?

My understanding is this is impossible, so more likely is that for some unknown reason the application is re-submitting this unique and performance destroying query, thus generating a new SPID, despite no user interaction.

  • 2
    Does your application have a connection timeout ( eg 30 seconds ) and retry logic? That could account for it. – wBob Apr 27 '16 at 22:24
  • Not sure... I suspect is the app connects once per request and has some logic to make a subsequent request if conditions are met. Given the answers this must be correct, at least the re-connect/submit aspect. =) – Dave Apr 27 '16 at 22:31
  • What are you using to capture the session information? Activity monitor, sp_who, sp_WhoIsActive, etc. ? – Jacob Barnes Apr 28 '16 at 4:22
6

No, the SPID (Server Process ID) is assigned the moment an application establishes a connection/session to the database and is retained until the connection/session ends.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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