How can I solve a connection pool problem between ASP.NET and SQL Server?

Question

i was wondering if you could help me with this. I've been struggling for two days now. I'm trying to build the models for movies, actors, and the studio on rails. So what i've got so far is that a STUDIO has_many movies and many characters (as in MCU has iron man, thor, hulk, etc as characters, or DCU has Batman, Joker, etc...). Also, a MOVIE has_many characters through a STUDIO. And a single character has_many MOVIES thorugh STUDIO.

so my design is something like this(Trying to design with postgresql database)

rails g model studio name:string movies_ids:Array characters_ids:Array #movies_ids will hold an array of all the ids of the movies that 
                                                                       belong to the studio, same goes for characters_ids

rails g model movie title:string studio_id:integer characters_ids:Array #a movie belongs to only one studio, 
                                                                        but has many characters, so an array of all the characters_ids.

rails g model character name:string studio_id:integer movies_ids:Array #a character belongs to a single studio but can appear in many movies, 
                                                                        so an array of movies_ids

Is this model designing correct? am i missing something? by now you have probably realised i'm a newbie.

So, after that, in my models i would have something like:

class Character < ApplicationRecord
    belongs_to :studio
    has_many :movies, through: :studio
end

class Movie < ApplicationRecord
    belongs_to :studio
    has_many :characters, through: :studio
end

class Studio < ApplicationRecord
    has_many :movies
    has_many :characters
end

What i want to achieve is to be able to say Studio.Movie.all() and retrieve all the movies from a studio. Also Movie.characters.all() and retrieve all the characters from a movie And also Character.movies.all() retrieves all the movies a characters has been in. In the future i will have many Studios, so DCU, MCU, Warner, Pixar, etc...

If you could guide me through just a bit or at least point me in the right direction i'd really appreciate it. I'm having a hard time grasping on how to achieve this. I've been using different youtube videos to guide me through but none of them are desingning something like what i'm trying to do.

Solution 1

In most cases connection pooling problems are related to connection leaks. Your application probably doesn't close its database connections correctly and consistently. When you leave connections open, they remain blocked until the .NET garbage collector closes them for you by calling their Finalize() method.

You want to make sure that you are really closing the connection. For example the following code will cause a connection leak, if the code between .Open and Close throws an exception:

var connection = new SqlConnection(connectionString);

connection.Open();
// some code
connection.Close();                

The correct way would be this:

var connection = new SqlConnection(ConnectionString);

try
{
     connection.Open();
     someCall (connection);
}
finally
{
     connection.Close();                
}

or

using (SqlConnection connection = new SqlConnection(connectionString))
{
     connection.Open();
     someCall(connection);
}

When your function returns a connection from a class method make sure you cache it locally and call its Close method. You'll leak a connection using this code for example:

var command = new OleDbCommand(someUpdateQuery, getConnection());

result = command.ExecuteNonQuery();
connection().Close(); 

The connection returned from the first call to getConnection() is not being closed. Instead of closing your connection, this line creates a new one and tries to close it.

If you use SqlDataReader or a OleDbDataReader, close them. Even though closing the connection itself seems to do the trick, put in the extra effort to close your data reader objects explicitly when you use them.

---

This article "Why Does a Connection Pool Overflow?" from MSDN/SQL Magazine explains a lot of details and suggests some debugging strategies:

• Run sp_who or sp_who2. These system stored procedures return information from the sysprocesses system table that shows the status of and information about all working processes. Generally, you'll see one server process ID (SPID) per connection. If you named your connection by using the Application Name argument in the connection string, your working connections will be easy to find.
• Use SQL Server Profiler with the SQLProfiler TSQL_Replay template to trace open connections. If you're familiar with Profiler, this method is easier than polling by using sp_who.
• Use the Performance Monitor to monitor the pools and connections. I discuss this method in a moment.
• Monitor performance counters in code. You can monitor the health of your connection pool and the number of established connections by using routines to extract the counters or by using the new .NET PerformanceCounter controls.

Solution 2

Upon installing .NET Framework v4.6.1 our connections to a remote database immediately started timing out due to this change.

To fix simply add the parameter TransparentNetworkIPResolution in the connection string and set it to false:

Server=myServerName;Database=myDataBase;Trusted_Connection=True;TransparentNetworkIPResolution=False

Solution 3

Did you check for DataReaders that are not closed and response.redirects before closing the connection or a datareader. Connections stay open when you dont close them before a redirect.

Solution 4

Unless your usage went up a lot, it seems unlikely that there is just a backlog of work. IMO, the most likely option is that something is using connections and not releasing them promptly. Are you sure you are using using in all cases? Or (through whatever mechanism) releasing the connections?

Solution 5

We encounter this problem from time to time on our web site as well. The culprit in our case, is our stats/indexes getting out of date. This causes a previously fast running query to (eventually) become slow and time out.

Try updating statistics and/or rebuilding the indexes on the tables affected by the query and see if that helps.

View more solutions

Comments

[splattne]

The Max Pool Size default is 100 if I correctly remember. Most Web sites do not use more than 50 connections under heavy load - depends on how long your queries take to complete. Short term fix in Connection String: try to set a higher value in your connection strings: "Max Pool Size=..."

[Amr Elgarhy]

how much? make it 200 for example?

[splattne]

I think that you should really search for what's causing the problem. Are your queries (or some of them) running very long?

[Amr Elgarhy]

may be thats the real reason, a query which is taking to much time to execute, i will search in that, thanks

[splattne]

I hope you can find the problem. A suggestion: if you're using SQL Server, try the "SQL Profiler" and look for long queries: sql-server-performance.com/articles/per/…

[Alex Czarto]

Could be your statistics that need to be updated.

[splattne]

+1 - Or functions returning DataReaders - Connection will never close outside the function you created them...

[Amr Elgarhy]

We are using LLBL and the website is running from 2 years and just the last few days started to act like this.

[Amr Elgarhy]

Whats the recommended MaxPoolSize?

[mmx]

Probably, the best way to go is not to specify it unless you have special requirements and you know a good pool size for your specific case.

[Amr Elgarhy]

Note: i checked and i found that the active connections to my db are around 22 live one, is that too much?

[mmx]

I don't think so. The default pool size is 100 connections I think. It depends on the load of each connection on the network and SQL server. If those are running heavy queries, it might cause problems. Also, network issues might occur when initiating a new connection and may cause that exception.

[LukeH]

A small correction: the GC never calls an object's Dispose method, only its finalizer (if there is one). The finalizer can then do a "fallback" call to Dispose, if necessary, although I'm not certain whether SqlConnection does so.

[DrGriff]

I think that this would explain why a query could time out, but I don't think that this would explain why a time out is experienced whilst trying to obtain a connection.

[live-love]

If your function returns SqlDataReader you are better off converting it to DataTable than raising the max pool size

[LosManos]

It could be that with a bad index the queries take longer and more connections are used at the same time.

[boateng]

Worked for me after updating all statistics with sp_updatestats on a db: EXEC sp_updatestats;

[Dib]

Maybe I am missing the point of SqlConnection.ClearPool, but doe that just prevent your current connection being released back to the connection pool? I thought the idea of the connection pool was to allow quicker connections. Surely releasing the connection from the pool each time it is finished with means a NEW connection will need to be created EVERY TIME one is needed, instead of pulling a spare one from the pool? Please explain how and why this technique is useful.

[mahesh sharma]

Is there any kind of performance degradation when we have to set Max Pool size 50 and we have only few users.

[FBryant87]

In this case the issue is "The timeout period elapsed prior to obtaining a connection from the pool". Did your connection string fix resolve this, or was it a separate handshaking issue?

[ajbeaven]

From what I remember it was the exact same error message as in the question. It occurred immediately after updating to .NET Framework v4.6.1.

[Steve Kennaird]

This was the case for me too, fixed it for me on an App Service I was running on Azure, connection to an Azure SQL database. I was using Dapper and correctly disposing of connections, yet still got the "timeout period elapsed prior to obtaining a connection from the pool" error message. But no more, so thanks @ajbeaven

[Mick]

Would switching the connection string from the NETBIOS name to the IP Address also solve this issue?

[Henrik Høyer]

This Microsoft article explains why this setting might help techcommunity.microsoft.com/t5/sql-server-support/…

[Tiyyob]

However, if i use "commandbehavior.closeconnection" in "ExecuteReader()" function, it will close the connection once close method of reader is called.

[GuidoMedina]

Thank you for using your time to help me out. I will try your way of making the associations as they seem to make the most sense. About the " rails g model character name:string movie:belong_to " doesn't this map a one-to one relationship between a character model and a movie? as in a single character can belong to only one movie? For example: Iron man belongs to the movie Iron man 1, but i also have iron man 2 and iron man 3, so when i try to map iron man to the three movies, and then use Character.where(name:"iron man").movie.all() will i still get the three movies? or only one?

[Pedro Schmitt]

Yes, you are right, the way I wrote It will be one-to-one. Now I understood that you need to have the same Iron man character record linked to a lot of different movies. It will be necessary to a join table. You can create a CharacterMovie join model, with rails g model CharacterMovie character:belongs_to movie:belongs_to. And add a 'has_many: movies, through: :character_movies' on Character and 'has_many: characters, through: :character_movies' on Movie. Because if you use Studio as a join table, when querying something like Movie.characters.all you will receive all chars from that studio.

[GuidoMedina]

Well i followed your advice and created a separate join table for basically everything lol. It worked. I have a join table for Studio and Movies (studio_movies) and a join table for Movies and Characters (movie_characters) and some more models and join tables and now my relationships are working as desired. I can now call Studio.movies and get a list of movies and also i can call Character.movies or Movie.characters. Thank you for your help. Now i have another issue with rendering my data as JSON and i made another thread for that.

[Pedro Schmitt]

Happy to know @GuidoMedina! If possible, mark this answer as accepted!