KILL SESSIONS
-
Hi All,
In one of our production environments we are seeing some session(s) which are there for more 15 days and current state is "sleeping" doing nothing. These processes/spids are coming a vendor application.
We tried reaching application team and they have no idea of what to do and they are asking from db team.
I feel there is some problem from the application side in regards to closing the connection once its finished its execution still holds some locks,memory etc...
Is there anything we can collect or ask the application team to check? For example, if the query is finished its execution , then ask them to do some data validation to confirm if query has to done the work of what it is supposed to do.
Using any DMV, can we know for long it been is "idle" state doing nothing? Is there anything else we need to consider before killing such spids.
If so, we can safely KILL that process. We dont want to keep the stale connections holding server resources.
Thanks.
Regards,
Sam
-
Are you sure it's not just a connection pool of some sort? Killing such spids could cause a bit of slowdown.
The opposite of that beneficial thing is that it could be a "connection leak" in the code.
I'm not the guy to ask how to tell the difference, though. I'm not the one that's had to chase down and prove such things.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff. Will those connections have any negative performance on SQL Server or is it normal? What is the benefit that application would get?
-
If it's a connection pool, then you'll know the benefit... faster connections for your users.
If it's a connection leak, you'll eventually run out of connections.
The disadvantage is that each connection takes some system memory. I don't remember how much. Someone else could answer that question better than I or you could do a search for that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - vsamantha35 wrote:
Thanks Jeff. Will those connections have any negative performance on SQL Server or is it normal? What is the benefit that application would get?
The application may be assuming that it's connections will stay open and might not respond gracefully to them being randomly killed on the DB server. There's nothing inherently wrong with having a connection open for an extended period of time.
-
Connecting is expensive. Staying connected is cheap. I'm not sure about SQL Server 2014, but a connection in prior versions consumed only 32KB of memory.
Check sys.dm_exec_sessions.last_request_end_time for the connections. If that value changes periodically, then the sessions are being reused and all is good.
Like Jeff said above, if the count of idle connections continuously rises and never falls over a long period of time, and the sys.dm_exec_sessions.last_request_end_time of the idle connections never changes, then you're leaking connections.
Eddie Wuerch
MCM: SQL -
Thanks for the clarifications. Many thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply