API Server Cursors - SQL Server Sessions

[azuchetto]

While analyzing Process/Session Information in SQL Server Current Activity over the last several months on our ERP's SQL Server I have noticed that the number of sessions open starts at a very low number after a system reboot (150), then slowly grows as users login and system activity increases. This seems very normal, except that over the next few weeks the number slowly increases (3500) until we reboot.

After looking at the sessions closely many of them have login times and last batch times that are the same, which tells me that a connection was made, but nothing has really happened since. For example, many of the sessions were opened 5 days ago and the last batch run by these sessions were 5 days ago too. Most of them are have a last SQL command batch ran as “sp_cursorclose;1”, ”sp_cursorunprepare;1”, or ”sp_cursorexecute;1”.

Performance definitely degrades over time as the number of sessions increases, until we reboot. Also, you would expect to see this number of sessions decrease during slow times, but this does not occur. Could something be mistakenly leaving connections open?

SQL Server 2000 sp3
Windows Server 2000 sp3
ERP - PeopleSoft EnterpriseOne XE

[rmiao]

Should check with vendor to see how the app connects to sql, and if the app closes connections properly.

[azuchetto]

I am currently waiting for an answer from our ERP software vendor (PeopleSoft). I was hoping some API Server Cursor experts would have some ideas on what may be causing these connections not to be closed. I realize that it is most likely a problem with our ERP System and not SQL Server.

[Rawhide]

Have you tried manually killing the idle connections? If killing them doesn't resolve your performance degradation, then the number of idle connections is merely a coincidence. Connections do use a little bit of memory, but I don't see how an idle connection would be degrading performance.