SQL Server Connection Pooling Myths

The results pictured below are that two simultaneous connections were created for 1000 identical connection requests (Figure 1a). In SQL Enterprise Manager we can look at these two connections (Figure 1b) and see what they were doing (Figure 1c, 1d). So far nothing interesting; we saw the same SQL statement executed repeatedly. It should be noted that the speed of your workstation and database server might affect how many connections are pooled. A fast workstation working against a slow server may make open requests faster than a connection can be repooled. The closing and repooling of a connection might not happen fast enough for the application, so SQL will allocate another connection.

For this reason, a very simple delay tactic (write out a space character and flush it to the browser) is employed. Also, should you wish to test your server’s connection pooling, be sure to let your test connections die off. SQL connections will close themselves after going unused for 60 seconds. You can wait the minute, or you may also choose to kill them yourself from Enterprise Manager (Management folder, Current Activity, Process Info). You may also have latent connections open that will skew your results. If you have SQL Agent running, SQL Enterprise Manager open, or SQL Query Analyzer open, you should close them before running these tests as they all hold connections open.

Figure 1a. Performance monitor showing our two connections opening, then going out of scope.

Figure 1b.  Process information about our two connections.

Figure 1c: Process detail from connection above.

Figure 1d: Process detail from connection above.

Question 1: True or False: Changing the connection string, even slightly, will force a new connection.

You don’t believe me? You’re a skeptic. Good. This is a common misconception. Many sources report that modifying even a single space in your connection string will force new connections to be created. While it is true that modifying the credentials of a connection string will create a new connection, shuffling the parameters around will not. SQL simply verifies that the user, password, and database are the same for a connection request to be eligible for pooling.

Now let’s prove it. The code below uses four different connection strings and executes four different SQL statements. If an altered connection string forced a new connection, we would expect there to be at least four connections created. As it turns out, there are only two. We can verify our open connections in Performance Ponitor and see for certain that never more than two connections are opened.

Additionally, SQL connections will close themselves after going unused for 60 seconds. Armed with this information, we can be absolutely certain there aren’t other connections being opened and closed without our knowing it. Here’s the test code for this question.