LINQ to SQL and Connection Pooling
I’ve been working with Microsoft’s database connection technologies (ADO.NET, ADO, and ODBC) for many years now. A customer pinged me with an interesting question about LINQ and database connection pooling in an ASP.NET application. I thought I would simply copy and paste and hope this helps the rest of the world.
Connection Pools, ADO.NET, and IIS
Connection pools are managed on a per-application pool basis in IIS, so each IIS application pool will maintain its own database connection pools. All database connections with exactly the same connection string and credentials are automatically pooled, this is handled by the underlying provider. There are some configuration options you can provide, such as min or max pool size, but the general recommendation is to use the defaults when dealing with web applications. See the following article:
https://msdn.microsoft.com/en-us/library/8xx3tyca(VS.71).aspx
One thing to look at is how you are forming the connection string. If you have any variation in your connection string (using different user names in the string, for example), it can lead to an exhaustion of resources. The common guidance is to use a single connection string stored in the configuration file that uses integrated authentication (does not contain a user ID or password in the query string, the identity is determined based on the identity of the IIS application pool user account). A way to get an idea of the connections and processes is to go into SQL Server Management Studio and run the following query:
SELECT spid, uid=rtrim(loginame), pname=rtrim(Program_name),
dbname=db_name(dbid), status=rtrim(status) FROM master.dbo.sysprocesses
On my laptop that is running SharePoint, SQL Server Reporting Services, and a custom application, the list looks something like this (with the 34 background worker processes not shown for brevity). I highlighted my custom application that is connecting to my WFTracking database used for Windows Workflow Foundation tracking queries.
LINQ and Connection Pooling
I’ve seen questions from customers asking if they should be doing anything special to clean up after LINQ to SQL queries. The #1 thing to make sure is that you are properly closing the resources and not trying to reuse the DataContext or SqlConnection. By default, LINQ will properly clean up the resources, but there are cases where the connection can be left open, depending on the usage of your code (reusing the DataContext, creating a connection using an existing SqlConnection object, or using MARS).
https://msdn.microsoft.com/en-us/library/bb386929.aspx
Remember that the SqlConnection type in .NET is designed to be a very short-lived object due to its pooling behavior at the provider level and therefore should be opened just before you need it and closed just after you use it in the context of an ASP.NET web application. Do not try to cache a SqlConnection object in your ASP.NET code. The guidance for LINQ is similar, don’t try to cache the DataContext, simply create a new DataContext using the same connection string (note: using a string is not the same as using a SqlConnection object!) When using LINQ to SQL, this means frequently creating and destroying the DataContext. Do not try to reuse the DataContext in your code, simply recreate it each time you query the database.
ADO.NET Trace Logging
If you are encountering odd connection behavior with LINQ or ADO.NET, you have a fantastic tool to diagnose issues at your disposal using ADO.NET Trace Logging. The following article shows how to enable ADO.NET trace logging, enabling you to capture low-level provider details.
https://www.developer.com/net/csharp/article.php/3723011