-
connections to sql server
I want to monitor how many users are connected to a thrid party application over a number of weeks. The application is web (intranet) based, uses NT authentication and uses a number of databases, all on one SQL 2005 instance. I created a job that runs a stored procedure in the master db that runs every 15 mins. This sp stores the results of the following query:
SELECT @currdtm AS CountDatetime, login_name
FROM sys.dm_exec_sessions
WHERE session_id >= 51
AND login_name <> 'NT AUTHORITY\SYSTEM'
AND CHARINDEX('\', login_name) > 0
AND CHARINDEX('Administrator', login_name) = 0
GROUP BY login_name
This works perfectly, except over time the number of connections grows steadily whereas it should be linear. This is most evident at night where the number of connections should drop to one or two but has grown so that the minimum is now eight. I can see these connections in Activity Monitor but the users are not logged into the application. Is SQL Server holding these connections open for performance related benefits or is the application to blame?
On the web server, there is a screen to view current connections and it displays what I believe are the actual live connections.
-
I suppose I could add a filter on login_time or last_request_start_time to only give me todays connections. Still and answer on what's happening with the connections would be nice.
-
If app uses connection pooling, it will not close connection when user logs out. It can reuse those opened connections for other users.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|