Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Windza
Yak Posting Veteran
61 Posts |
 Posted - 2013-07-19 : 20:57:33
|
| 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.sysprocessesWHERE dbid > 0GROUP BY dbid, loginamebut 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
61 Posts |
Posted - 2013-07-19 : 22:16:04
|
| I think I might've found some answers here;http://msdn.microsoft.com/en-us/library/ms176013.aspxI'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 :-) |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2013-07-21 : 21:25:14
|
| 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. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2013-07-23 : 00:57:52
|
| 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... |
|
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2013-07-23 : 01:01:55
|
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 ;-/ |
|
|
|
|
|
|
|
|
Connections to SQL Server...