SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Connections to SQL Server...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Windza
Yak Posting Veteran

Australia
61 Posts

Posted - 07/19/2013 :  20:57:33  Show Profile  Reply with Quote
I have a very limited understanding of how SQL connections work, but based on observation of the sysprocesses table I figure it's a safe assumption that SQL opens/closes connections on-demand depending on the frequency of requests made over the connection.
Here's a common scenario that I think I'm seeing; I open a remote application, it authenticates and connects to SQL, makes a few requests and then sits idle for 1 minute (but is still open on the remote client)... on the SQL side of things, SQL appears to close the connection after ~30 seconds until another request is made at which point it will rapidly re-open.

What I'm curious about is if SQL keeps a record of applications that have gone idle... such that I can view a list of 'pending' connections if you like.

I know I can view "active" connections using this statement;

SELECT
db_name(dbid) AS [Database Name],
count(dbid) AS [No Of Connections],
loginame AS [Login Name]
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame

but idle applications disappear from the list when SQL closes the connection.
It may be that a closed connection is simply that, but I wouldn't have thought a client application would have to authenticate every request it makes?
If anyone can provide some insight here it would be much appreciated.

Windza
Yak Posting Veteran

Australia
61 Posts

Posted - 07/19/2013 :  22:16:04  Show Profile  Reply with Quote
I think I might've found some answers here;
http://msdn.microsoft.com/en-us/library/ms176013.aspx

I'm still not sure if I'm 100% with this, but I was failing to distinguish between session and connection.
This query looks promising.

SELECT login_name , login_time, COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name, login_time;

Feel free to comment if you can clarify anything for me... thanks :-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37139 Posts

Posted - 07/21/2013 :  01:01:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
SQL doesn't open/close connections on demand. The client application determines when to open or close a connection. SQL just connects/disconnects the connection based upon the command the application sent.

If you are seeing the connection get disconnected from the SQL Server side, then it's because the client application sent a disconnect or the network dropped it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Windza
Yak Posting Veteran

Australia
61 Posts

Posted - 07/21/2013 :  21:25:14  Show Profile  Reply with Quote
Thanks Tara... do you know if there is anything to distinguish an 'application is idle' disconnect from a 'application is closed' disconnect or are both the same?
If they're both the same, that would mean every 'reconnect' would have to be authenticated (rather than just the session) - maybe that's valid but it seems like that would introduce unnecessary overhead.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37139 Posts

Posted - 07/22/2013 :  12:56:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
You need to read up on connection pooling.

SQL doesn't care if the application is idle. That connection remains open and usable until the application disconnects the session. A connection is a connection to SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Windza
Yak Posting Veteran

Australia
61 Posts

Posted - 07/23/2013 :  00:57:52  Show Profile  Reply with Quote
Tara, I'm not really too concerned with the technical details of connections in SQL...
What I want to know is if there's any way to identify sessions that are active in SQL?
The query I OP'd identifies active connections but when my application temporarily drops the connection it ceases to appear in the resultset... what I want is the resultset that shows the application hasn't closed the entire session.
If that makes any sense at all...
Go to Top of Page

Windza
Yak Posting Veteran

Australia
61 Posts

Posted - 07/23/2013 :  01:01:55  Show Profile  Reply with Quote
quote:
Originally posted by Windza

Tara, I'm not really too concerned with the technical details of connections in SQL...
What I want to know is if there's any way to identify sessions that are active in SQL?
The query I OP'd identifies active connections but when my application temporarily drops the connection it ceases to appear in the resultset... what I want is the resultset that shows the application hasn't closed the entire session.
If that makes any sense at all...



OK... I re-read your post and got a slightly different understanding. If I'm understanding it correctly, the answer to what I'm asking is "there isn't"... if an idle application disconnects, there is no data in SQL to identify it as idle - rather it's gone until it reconnects... sheesh - not sure why I'm having trouble grabbing this concept ;-/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000