October 25, 2002 at 8:28 am
There is a relationship between the three, but it is fuzzy to me.
The MS doc I've gone over suggests that there is a SPID per connection. However, we've seen instances when there is more than one SPID per connection.
Looking at Profiler, we've seen an Audit Login/Logout that seem to correspond to a connection.
Steve
Steve Miller
October 28, 2002 at 3:04 am
If you have a multi processor box, sometimes when you do an sp_who2 you will see what appears to be multiple spids per connection.
In fact they are all the same spid, but show that SQL Server is parallel processing the query over more than one CPU.
Maybe that's what you are seeing?
October 28, 2002 at 4:33 am
The ECID column of sysprocesses identifies sub threads. Not quite related but I'll throw in, not unusual for either the app or a component of the app to open a 2nd background connection to do some work, grid population for example.
Andy
October 28, 2002 at 7:29 am
>> not unusual for either the app or a component of the app to open a 2nd background connection to do some work
I suspected that, but wanted someone to say it without me suggesting it. But are you saying the front end is doing that, or SQL Server? The reason I ask is that our C++ coders, who are coding the front end, believe they have only one connection.
Steve
Steve Miller
October 28, 2002 at 7:49 am
If the app is doing it you should see a second (or more) spid. If SQL is using parallel execution you'll have multiple lines in sysprocesses with same spid, different ecid.
Andy
June 14, 2007 at 6:46 am
I'm a newbie and thought I could chime into this conversation as I too am dealing with multiple spid's per user. The ECID's are all 0 and I am trying to Kill processes (user logins) that are idle for more than 4 hours. On these multiple processes per user, one spid may exceed the time but the others are OK. How safe is it to kill the timed out processes while the other processes are still within the time limit?
I would appreciate any advice.
Thank You.
Emma
June 14, 2007 at 7:03 am
If you have a connection that has executed and is not runnable you can check the coomand that it has executed and kill the spid. But check twice before you do one like that.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply