News, examples, tips, ideas and plans.
Thoughts around ORM, .NET and SQL databases.

Sunday, November 17, 2013

Avoiding connection pool fragmentation


Microsofties admitted that some noticeable performance problems are possible while working with MS SQL Server via ADO.Net (link, scroll to Pool Fragmentation). The connection pooling technique used in ADO.Net to optimize and minimize the cost of opening connections reported to be "not so optimal" in particular scenarios.

The root of the problem is that the exact match between connection string and corresponding connection pool is required. Even if 2 connection strings are build from the same key-value pairs but differ in their order, you get 2 connection pools.

As a result, we get connection pool fragmentation, which is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance. Surprise for large multi-tenant application developers and maintainers!

From their side, Microsoft is not going to do anything about it in foreseeable future, instead they suggest to use a workaround: connect to master database and open the desired database with a separate command:

// Assumes that command is a SqlCommand object and that
// connectionString connects to master.
command.Text = "USE DatabaseName";
using (SqlConnection connection = new SqlConnection(
  connectionString))
  {
    connection.Open();
    command.ExecuteNonQuery();
  }

How can you achieve the same not leaving the zone of comfortable DataObjects.Net API? Time to get familiar with new member of DomainConfiguration class: ConnectionInitializationSql. The main purpose of the member is to literally execute the provided text as DbCommand right after a connection is opened.

Say, we are using Northwind database:


<domain
name="Default"
provider="sqlserver"
connectionString="Server=myServerAddress;Database=master;..."
connectionInitializationSql="USE NORTHWIND"
...>
<domain>


The feature is available in DataObjects.Net 4.6.4. Download DataObjects.Net

3 comments:

  1. Wow, thanks! Are you one of these learners? If yes, you should take ghostwriting agency choice seriously. For you to know, our ghost writing site has a high customer satisfaction rate. Our college writing services hire only subject-specific ghost writers. Struggling to compose a thesis with no success? Don’t panic; we are the best term paper writing service that aims to help students get their dream grades and improve their overall performance.

    ReplyDelete
  2. Awesome post, very informative indeed! Thank you for sharing!
    www.curatedleads.org/landscaping-leads

    ReplyDelete

  3. Anyone who is interested in learning about this service and which name is the best essay writer should contact me because I have a lot of work to do and I always conduct my academic work here "

    ReplyDelete