i've got SQL 2000 on a server with 1GB ram. JSP & CF code is accessing this server & DB's. We are finding that a lot of connections are staying persistant for a long time (especially with the JAVA Apps).
I'm currently able to monitor this using sp_who2, performance monitor and the following SQL call:
SELECT * FROM master..sysperfinfo
(object_name = 'SQLServer:General Statistics' and counter_name = 'User Connections') OR
(object_name = 'SQLServer:Memory Manager' and counter_name = 'Connection Memory (KB)')
My questions are:
Is there a better way to monitor this?
Is there a way to terminate a session and how can i tell it's idle time?
Any performance tweaks you can offer as well would be MOST appreciated.
another note here. i came into work this morning and had 108 connections to the SQL server. and it was useing abou 300KB of mem. currently it's at 103 connections and 2920 KB of mem. I've got a dual processor system with 1 GB of ram and my applications are dragging.. it only gets this way when my connections get above 100. any ideas???