When using ADO.NET to make SQL Server database connections, connection pooling is used to minimize the cost of repeatedly opening and closing a new database connection. For a full explanation of connection pooling, see SQL Server Connection Pooling (ADO.NET)

The default maximum size of a connection pool is 100. If a connection is not properly closed/disposed in application code, it can remain in the connection pool unused until garbage collection cleans it up manually. The garbage collection process can take several minutes before closing an open connection. In a busy application with many database calls, the connection pool default max of 100 can be exhausted quickly if connections are not closed/disposed properly. When this happens, a typical exception would be: “Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.”  

I recently encountered a scenario like this with extensive and complex application code that made connections to multiple SQL Servers and databases, so tracking down the improper connection string in the application code was proving difficult and time consuming. To aid in the troubleshooting, I created the PowerShell script below that monitors connection pool size and usage by host & host process id in order to narrow down the source of improper connection string(s).

To execute the script, two input files are necessary. First, create a “Servers.txt” file containing all the SQL Servers that your applications(s) make connections to. The Servers.txt file should be saved in the same directory as the PowerShell script and should look like this (Just replace “Server#” with your real server names.):

Secondly create a “FunctionLibrary.ps1” file containing the Invoke-SQLCmd2 and Write-DataTable custom functions. For more on adding these two functions and their use, see a full and thorough write up at the Hey, Scripting Guy! Blog. (Note that I’ve leveraged some of the concepts from this blog post into my own.) This FunctionLibrary.ps1 file should also be saved in the same directory as the other files listed in this post.

Finally, save the PowerShell Script below as a .ps1 file. (In the example shown here, I’ve named the file MonitorConnectionPools.ps1) Just replace YOUR_SERVER and YOUR_DATABASE with a real server name and database in your environment. A great place for this table would be on a DBA utility server or some other non-production server you use for administrative tasks.

#Add Functions
. ./FunctionLibrary.ps1</pre>
$ErrorActionPreference = "Stop"
$ServerList = Get-Content Servers.txt

$Query = "DECLARE @CurrentDate DATETIME = GETDATE();
SELECT  @@SERVERNAME AS 'ServerName', login_name, Host_name, host_process_id, COUNT(*) AS 'ConnectionCount', @CurrentDate AS 'CollectionTime'
FROM sys.dm_exec_sessions 
WHERE host_process_id IS NOT NULL
GROUP BY Host_name, login_name, host_process_id
HAVING COUNT(*) >= 25;"

foreach ($Server in $ServerList)
{
$Results = Invoke-Sqlcmd2 -Server $Server -Database master -Query $Query -As 'DataTable'

Write-DataTable -ServerInstance "YOUR_SERVER" -Database "YOUR_DATABASE" -TableName "ConnectionPoolMonitor" -Data $Results
}

 

Now that the PowerShell setup is in place, the next step is to create a table to store the results. This table should be created on the server you specified for YOUR_SERVER  in the database specified as YOUR_DATABASE in the MonitorConnectionPools.ps1 script above. To create the table structure, run the following SQL script:

SELECT  @@SERVERNAME AS 'ServerName'
,login_name
,host_name
,host_process_id
,COUNT(*) AS 'ConnectionCount'
,GETDATE() AS 'CollectionTime'
INTO [YOUR_DATABASE].dbo.[ConnectionPoolMonitor]
FROM sys.dm_exec_sessions 
WHERE host_process_id = -1
GROUP BY Host_name, login_name, host_process_id

Note that there is not a host_process_id of “-1”, so the SELECT returns no rows. This just creates the table with the desired structure in preparation for the real writes that will take place later.

Lastly, set up a SQL Server Job that calls your newly created MonitorConnectionPools.ps1 script. Set the Job up as follows:

In the example above, I’ve saved the MonitorConnectionPools.ps1, FunctionLibrary.ps1, and Servers.txt files in a local folder called “C:\Powershell\MonitorConnectionPools“. Use the Set-Location command to navigate to your saved file location.  Schedule the SQL Job to run every minute (or two) and it will write connection counts per host & host_process_id to the ConnectionPoolMonitor table created above. The script only collects connections in excess of 25 to avoid excess noise in the table. You can adjust the threshold as needed in the MonitorConnectionPools.ps1 file.

Once the SQL Job has been running for some time, review the data collected by querying the ConnectionPoolMonitor table. Look specifically for any instances of “100”  (or close to it) in ConnectionCount. If any connection pool timeout errors are seen in conjunction with the 100 connections, there’s likely an ADO.NET connection string that is missing a proper close/dispose command.

In the example above, host process 4732 using the login name “App_Login” running from the server “App_Host” is consuming all 100 of the connections available in the connection pool. If connection pool timeouts are seen during the time period associated with the CollectionTime above, this would be the culprit.

To determine exactly what the host process is (in this case, host process 4732), run the Get-Process -Id 4732 PowerShell command on the App_Host server. If the process is an IIS Worker Process (w3wp.exe), one more step is necessary to determine the correct application pool that is causing the issue. From the Command Line on the App_Host server, navigate to the inetsrv folder and execute appcmd list wps to get the name of each worker process:

Names obfuscated to protect the innocent. 🙂

This will show you the name of the application pool with the improperly closed/disposed connection so it can be easily located (and hopefully fixed!) in the application code.

This post is full of a lot of information, but hopefully it will help you on your way to monitoring and troubleshooting ADO.NET connection pooling with SQL Server!