Increase SQL Server max pool size

Another lesson learned: Increase the Max Pool Size setting on systems that does a lot of requests to the DB if you use ADO.NET

I had problems earlier this week with a custom session management system that handles 15.000.000-20.000.000 requests per day per machine but is designed to handle a lot more.  The DB is pretty well optimized so call times for each query is only a few milliseconds in normal conditions. With 17.500.000 requests per day there are ~200 requests per second to the session management system with peaks that are a lot higher due to traffic distribution throughout the day.

I didn't realize that the ADO.NET Sql Client by default uses max 100 items in the connection pool. I expected it to grow the connection pool as needed to handle the increased traffic. I was wrong. When it reaches 100 connections it starts queuing requests. With >200 req/s it quickly reached the 100 item limit when the DB slowed down during batch operations.

The system scales a lot better with increased traffic after we doubled Max Pool Size setting in the SqlClient ConnectionString like this: 

Data Source=...;User ID=...;Password=....;Initial Catalog=...;Min Pool Size=10;Max Pool Size=200