DB connections are closing causing slow performance: MS SQL SERVER

I have two applications that uses the same 2 databases. 1 application runs fine, no performance issues. When i run the query below i get 1 connection per database. They stay open for a while. So as I browse it seems that those two connections are reused. After a while they close.

When I run the second application the connection is opened and immediately closed. I notice that when I run the query below the values of the number of connections increases while the page is refreshing and as soon as the page is done loading the number of connections is reduced.

I added this to the connection string:

   Max Pool Size=75;Min Pool Size=5;

but the number of connections now jumped from 0 to 5 and then jumps back down to 0

This is causing the app to be very slow as some pages have many queries in them.

Any help on resolving this issue would be appreciated.

The query:

SELECT
   DB_NAME(dbid) as DBName, 
   COUNT(dbid) as NumberOfConnections,
   loginame as LoginName
  FROM
   sys.sysprocesses
 WHERE 
   dbid > 0
GROUP BY 
   dbid, loginame   

Answers


@User1253073 -> From a best practice perspective you should open and close connections on both applications only when required. Something like: a) Open a connection, execute a query and load the results into a DataSet while disconnected b) Close the connection and do stuffs to manipulate the DataSet results. c) continue the code. This approach would reduce the number of alive connections to the database with no reason and a improve apps performance.


Yeah I know. The problem was with the application pool, I created a new application pool and it fixed it. Not sure what was the issue though. – user1253073 just now edit


I have the same issue. Mine is in a .NET Windows service written in C# doing some intensive updates on a SQL Server 2008 database constantly : around 100k queries in a minute.

Usually the number of connections open in the pool is the max : 200. Every week, during around one hour, the connections in the pool are immediately closed after each command and reopened when some new command needs to be executed. It slows down the service dramatically.

I tried to change the pool Min and Max size and it does not change anything. The problem is impossible to reproduce easily : it just happens around once a week. For the moment, we didn't find any way to figure out who decides to close the connections : the .NET pool or SQL server.


Need Your Help

Error in Cursor in getting data from SQLiteDatabase

android android-listview android-sqlite android-fragmentactivity android-cursor

I want populate my ListView with the data from SQLiteDataBase but I'm having error on my cursor. I am a beginner in android programming please help. thanks.

Get Folders WITHOUT documents

php mysql

I use this query to get the number of documents the folder contains: