1

I wanted to understand what will be the impact of performance on server if i set Max Pool size =500 and Connection Lifetime=60 seconds in connection string specified in web.config

Is it dangerous to set connection max pool size as well as connection lifetime=60s ? I added this configuration as I was getting exception thrown for default connection max pool size i.e 200 so had to increase it to 500 though i am closing my connection properly as explained in other forums as well as Microsoft docs?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
user3239408
  • 105
  • 1
  • 8
  • 2
    I've been using SQL Server professionally for over 10 years and I've never once had a need to tweak these settings. What is your application doing that it needs 500 connections? Do you really have 500 concurrent statements going on at any given time? If not, you almost certainly still have a bug with connections being kept open somehow, even if you think you don't. Do you have solid reason to believe increasing it to 500 will not also simply error out later? – Jeroen Mostert Sep 12 '17 at 10:38
  • https://stackoverflow.com/questions/2748706/connection-timeout-and-connection-lifetime – TheGameiswar Sep 12 '17 at 10:39
  • Yes because if i use default pool size ie 100 than exception was getting thrown max pool size reached; than i set it to 200 than also i was getting an exception of max pool size reached; once i configured it to 500 and set connection lifetime to 60 sec i no longer get any exceptions related to max pool size reached; also to be clear i am closing connection properly as stated in microsoft docs as well as other forums – user3239408 Sep 12 '17 at 10:44
  • As for the impact on SQL Server, that's simple: more connections means more resource usage. Is that a problem? Depends entirely on your server and your load. SQL Server is designed to handle thousands of connections without a problem, there is no hard limit that will give it trouble. Of course, if you have 500 connections all hitting a database at the same time, you can start seeing I/O problems. But in general, it's a simple linear relation -- the client pool size has no impact on SQL Server, the total load on all connections does (whether pooled or not). – Jeroen Mostert Sep 12 '17 at 10:45
  • Ok is there a way i can monitor resources used by sql server; also is there any harm if i set connection lifetime i see many post where developers dont agree to set connection lifetime value instead use default value – user3239408 Sep 12 '17 at 10:52
  • If the connection lifetime is too long, you will unnecessarily hold physical connections open. If the connection lifetime is too short, you will create more TCP/IP connections than necessary, which slows down your application and can exhaust the ephemeral port range. I would err on the side of caution and not change the default lifetime at all, but if you do change it, adjusting it downwards is more likely to cause harm than upwards. Changing it has no impact on how many connections are available to your application, that's always covered by the pool size. – Jeroen Mostert Sep 12 '17 at 11:02
  • Is there a way to monitor all this? – user3239408 Sep 12 '17 at 11:09
  • Lots. [Activity Monitor](https://learn.microsoft.com/sql/relational-databases/performance-monitor/open-activity-monitor-sql-server-management-studio) is built into Management Studio, and all connection stuff has [performance counters](https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql) associated with it. – Jeroen Mostert Sep 12 '17 at 11:23

0 Answers0