1 - can't say for sure, I'd have to go find a server to dig into myself.
2 - yes, I see this periodically in my environment though we're not on sql 2012 yet on the systems we see this from. You may also want to check this post though State 46 seems to be related to having a specific Database=xxx in the connection string, does that db still exist?
The way my network is set up I suspect it's the network's automatic closing of tcp sessions after being 5 minutes idle that's the issue - neither the db nor the client is closing the session so the connection pool still thinks the connection is open and tries to use it only to find it's not really open anymore. You don't mention how the network between your web servers and db is configured, maybe your case is similar.
Another possibility may be the (old, not sure if ever really resolved, see http://support.microsoft.com/kb/942861) issue about TCP Chimney Offload settings.
3 - My understanding is pooling requires exact string matches, so whitespace and different order of parameters would cause different pools. (If I'm wrong on that, please let me know.)
How Connection Pooling works
The issue with connection pool fragmentation is that each connection pool is bound to the exact text of the Connection String, including the SID of the Windows Login when using a Trusted Connection / Integrated Security. The connection pool works because the connection is exactly the same in every way.
Hence, changing the database after connecting doesn't change anything from a security perspective. The advice to change the database after connecting (which can also be done via the SqlConnection.ChangeDatabase method), is to get around the fact that putting the "Initial Catalog" (or "Database") key/value pair into the Connection String means that those connections aren't using the exact same connection string.
With the above in mind, whether you are using Windows Logins (i.e. Trusted Connection / Integrated Security) or SQL Server Logins (specifying UserID & Password OR using a SqlCredential),
if the web app connects as the same Login for all requests, then as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get the most re-use out of connection pooling since all connections will be able to use the same pool. Else you will get a pool per Database.
if the web app connects with different Logins, then:
- in the one-database-per-client model (assuming they are only allowed to connect to their database, hence using a different Login per client), you can either always specify the same database for that Login, or not specify any database in the ConnectionString and only change the database context after making the connection, and you will get a pool per Login (or per Database: they are kinda the same thing in this model).
- in the multi-tenant model (assuming the app makes calls to multiple databases), as long as you don't specify the database in the ConnectionString (or at least always specify the same database), and only change the database context after making the connection, then you will get a pool per Login. Of course, if you specify the database in the ConnectionString, then you will get a pool per Login per Database!
What this all means in practical terms
No, you cannot have a single connection pool and "prevent the user from making cross-database queries". The very nature of a connection pool it is that it is the same connection because the connection is not closed. You cannot differentiate permissions when there is nothing to differentiate; the same Login is still logged in, just issuing another command.
Reducing the number of connection pools down to a single pool is at odds with the desire to have different permissions:
If you want to prevent a Login from accessing more than their database, or maybe confined to their database and any "shared" databases, that requires different Logins, and each one will have their own pool. If you go with Contained Databases, you still need to specify either the UserId & Password OR the Initial Catalog in the Connection String, and hence the result will still be one connection pool per each variation.
If you want to have just one connection pool, then you have no choice but to always use the same security credentials (regardless of them being based in Windows or SQL Server). And the same credentials means the same permissions.
The bottom line is, the wording of that MSDN article is a bit confusing / awkward, and you are over-estimating their advice. The issue of Connection Pool Fragmentation really shows up when:
- you are changing both the security credentials AND the "Database" / "Initial Catalog" across connections
- the app is multi-tenant (i.e. not one database per customer) such that each Login can connect to multiple Databases.
Having multiple Logins that connect to multiple Databases will give you more pools that are less frequently reused. Given that the unused connections linger for "approximately 4-8 minutes" (taken from the MSDN page you linked to, the "Removing Connections" section), this creates the inefficient situation of having too many idle connections.
But, having one Connection Pool per-Login OR per-Database is actually just fine.
Please note, however, that even with one Connection Pool per-Login OR per-Database, you can still experience too many idle connections if you have a lot of clients (e.g. Web Apps, back-end processes, etc.) hitting SQL Server from different servers.
Best Answer
Connection Pooling occurs completely at the client-side of an application, is seldom affected by the SQL Server directly.
Connection pooling depends on parameters in the connection string used by the client to connect to the SQL Server. Identical connection strings are typically candidates for pooling, assuming the following connection string settings allow pooling:
The SQL Server may be running out of service worker threads, if existing connections are not closed properly, which would typically result in connection problems. If the max worker threads server configuration option is set incorrectly, or too low, that might be a problem. The default value for this option is
0, indicating that SQL Server will automatically configure the value, according to these rules:You can check the configured value using this query:
Based on the error message, it looks like your client end is configured incorrectly, but that is only a guess. Check with your developers or vendor for help determining the cause of the problem.