SharePoint User Connections

A long time ago I learned a SQL Server performance guideline: User Connections should be well under 255. I think I learned that while working with SQL Server 6.5 or 7, and if it did go over 255, SQL Server would have to significantly increase the amount of memory used per connection to manage them all. In a variety of production systems I've monitored, I never saw User Connections even get close to that number, but that may have been due to the types of workloads they were under. Then the first time I started looking at performance counters on a production system that was a backend to SharePoint, I was shocked to see over 600 User Connections and while I watched it climbed to over 700. I thought something was wrong and tried to find out what it could be and had a very frustrating time trying to find any worthwhile information on the topic. Senior SharePoint architects and administrators seemed to know little or nothing about it.

So, here's what I think I've learned about it. A busy SharePoint system uses a very large number of connections, and it uses connection pooling by default, but it doesn't always look like pooling is on. You can use Performance Monitor or the equivalent to monitor the SQL Server: General Statistics: User Connections counter on the SQL Server box to see the number of connections. That number should float up and down, but not very sharply on a system under constant use. Look at the SQL Server: General Statistics: Logins/sec and the SQL Server: SQL Statistics: Batch Requests/sec to monitor the effects of SharePoint's connection pooling.

When the number of Batch Requests/sec is very low, Logins/sec and Batch Requests/sec may have almost identical numbers, but Batch Requests/sec can spike up while Logins/sec don't rise nearly as high. When they're close to the same, it means the number of queries SharePoint needs to process is below the number of connection pools it has open, so there's one request per connection pool. Then when the number of queries goes up, you start seeing multiple batch requests per login, so Batch Requests/sec looks like a multiple of Logins/sec. This is normal behavior, and a large, busy system can have thousands of User Connections, thousands of Batch Requests/sec, and hundreds of Logins/sec.

If I'm wrong on any of this, I'd appreciate you letting me know!