September 27, 2011 at 9:22 am
I'm curious about how connection pooling actually works because I'm not sure if my application is using it to our benefit [or at all].
My web application is running on 8 different servers that are load-balanced (web farm). On each server running the application, there are 4 threads running the app (web garden). So essentially, I have 32 separate threads running the web application.
I have a common assembly that does ALL of the database interaction, but all of the methods inside this assembly creates a connection to the database, executes the stored procedure, returns the data, then closes the connection.
Is connection pooling even being utilized in this configuration (I'm assuming the answer is no)? If the answer is no, is the fix for this to 1) In the global.asax file in the application_start method, create a database connection, then have all methods utilize that database connection variable? So I would then have 32 concurrent connections to the database that could be used depending on which server, and which thread gets the actual request?
Am I thinking of this correctly?
September 27, 2011 at 9:33 am
gregory.anderson (9/27/2011)
I'm curious about how connection pooling actually works because I'm not sure if my application is using it to our benefit [or at all].My web application is running on 8 different servers that are load-balanced (web farm). On each server running the application, there are 4 threads running the app (web garden). So essentially, I have 32 separate threads running the web application.
I have a common assembly that does ALL of the database interaction, but all of the methods inside this assembly creates a connection to the database, executes the stored procedure, returns the data, then closes the connection.
Is connection pooling even being utilized in this configuration (I'm assuming the answer is no)? If the answer is no, is the fix for this to 1) In the global.asax file in the application_start method, create a database connection, then have all methods utilize that database connection variable? So I would then have 32 concurrent connections to the database that could be used depending on which server, and which thread gets the actual request?
Am I thinking of this correctly?
Yes you are utilizing connection pooling. The way it works is that when a connection is closed, the pooling agent keeps it around until it times out or the pool needs to kill it for a new request. When a request is made for a sql connection it will first check the pool to see if there is one that matches the request (database, user, etc). If there is an existing connection it pool it will return that connection. What this does is helps ease the pressure on the server because establishing a connection is pretty costly so instead of creating a new one it reissues an existing, and unused one.
There are tons of articles out there that explain pooling in depth and more accurately than I did here. Here is a link to the MSDN article. http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2011 at 9:37 am
Appreciate the quick reply Sean, I'll read through that linked article also...
Would I see any benefit creating a connection at Application_Start, or would that even be of use since connection pooling is working?
September 27, 2011 at 9:56 am
You probably would not see any benefit from that, with the exception of the first user to access the system. Have you ever noticed that a web app takes a little longer to "spin up" after an IIS reset or the system is restarted??? Among other things sql connections are established. After the first page loads, subsequent page loads are a LOT faster due to resource recycling (some of which is sql connection pooling).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply