As part of my nHibernate troubleshooting, I decided to play with the max pool size setting in ado.net. We had a WCF Service going through nHibernate – which was setup to work against  SQLServer. 

<add name="myConnStr" connectionString="User Id=abc;Password=def;Data Source=mydb; Pooling=true;Connection Lifetime=0;Min Pool Size=2;Max Pool Size=100;Connection Timeout=60;Incr Pool Size=1; Decr Pool Size=1"/>

nHibernate and ADO.NET

How many database connections would be required for a single WCF Request? The answer depends on the Instance Context mode you are using in WCF (this depends on whether or not you are trying to maintain state between WCF calls – just like maintaining sessions in web requests).

WCF Settings (Per Call InstanceContextMode) –> Each Call spawns a new instance of the WCF Service –> Each WCF Instance –> One nHibernate Session –> Each nHibernate Session –> One Database Connection.

So, given that ONE WCF request corresponded to ONE SQL Server (ADO.NET) connection, our use case encompassed 300 simultaneous WCF Requests. This implied setting the ado.net connection pool size to 300. This turned out to not work as I had imagined. The pool manages its 100 connections in an optimal manner – and reuses connections for multiple requests. It seems that it is best to let ADO.net work with the default pool size. 

(Theoretical) Maximum Max Pool Size

The default pool size is 100 – which means that 100 sessions from nHibernate can simulataneously connect to SQL Server. On the SQL Server side, the max connections possible is 32768. So – in theory, one could increase the max pool size to 32768. However, as I discovered, increasing this pool size may actually be detrimental  to performance.

Summary

Increasing the max pool size in ADO.net does not necessarily buy you performance. It seems that leaving the default setting (100 connections) is where ADO.net’s performance is optimized.

Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.

Initial Consultation

Anuj Varma – who has written posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.

 Twitter Linkedin