MA2005


Hi

I would like to find out, what is the actual formula to set the right amount of connection pool size in a conn string. In my scenario, when 400 concurrent users are logged into the website, SQL Server starts timing out. I am thinking the following connection string should fix it, but I would appreciate if I can get some tips to optimize connection pooling. I really need to find out the criteria or the formula to set connection pool size in a connection string. I got the following conn string over the web, by just searching for optimized connection pools, but I am not sure if this will work. Also please explain max and min, and how what criteria do i use to set that size, I mean what does in the following example min pool size of 5 and max pool size of 60 means. Thanks.

conn.ConnectionString = "integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Min Pool Size=5;Max Pool Size=60;Connect Timeout=2;"; // Notice Connection Timeout set to only two seconds!



Re: SQL Server Connection Pooling

MA2005


Response from Bill Barnet (MSFT)

For the connection pool, I would recommend that you either use "Connection Per User" or a ratio more like 2 to 4 users per connection, so perhaps a Connection Pool Size of 250 for 750 users, though if you are not running out of memory there is also nothing wrong with making the connection pool too large. For more info on the connection pool options see my blog post at:

http://blogs.msdn.com/billbar/articles/517081.aspx

When you use a large connection pool, and exceed the threshold on Processor Time it means that the user load of 750 is causing whatever machine is exceeding the threshold to be very busy. Is this the load test machine or one of the servers under test If it is the load test machine that is too busy, you could perhaps reduce the CPU usage by increasing the think times for your test. However, if you believe the think time you are using are realistic and don't want to change them, depending on the processor speed of your load test machine, it may just be that 750 is about as many virtual users as it can simulate.

All of the threshold rules that are set by VSTS are in your .loadtest file. If you open the .loadtest file with the XML editor and search for "ThresholdRule" you can find them. You can also delete or change any of these if you don't like them. You can do this in the load test editor by expanding the tree under "Counter Sets" all the way down to the individual counter and selecting the threshold rule for those performance counters that have them defined.