Recipe 1.14 Monitoring Connections
Problem
You want to monitor
the
opening and closing of connections and the number of connections in
the connection pool while an application is running.
Solution
Use the Windows
Performance Monitor and the SQL Profiler to monitor connections and
connection pooling. See Recipe 1.15 for more
information on connection pooling.
Discussion
The following subsections discuss monitoring connection pooling for
SQL Server and ODBC .NET Framework data providers.
SQL Server
You can monitor SQL Server connections and connection pooling using
the SQL Server Profiler or the Windows Performance Monitor as
described in the following subsections.
1.14.3.1.1 SQL Server Profiler
To use the SQL Server Profiler to monitor connection pooling:
Start the Profiler using one of the following methods When the SQL Server Profiler appears, select File 
New Trace. Supply connection details and click OK. The Trace Properties dialog
box will appear. Select the Events tab of the Trace Properties dialog box. In the Selected Events list box, ensure that the
Audit Login and
Audit Logout events appear
beneath the Security Audit node. Remove all other events from the
list. Click the Run button to start the trace. The new Profiler window will display a table containing
Audit Login events when
connections are established and Audit
Logout events when connections are closed.
1.14.3.1.2 Windows Performance Monitor
To use the Windows Performance Monitor to monitor connection pooling:
Start Performance Monitor by selecting Start All
Programs Administrative Tools
Performance. Add performance counters to monitor connection pooling with one of
the following methods: In the Performance object drop down list, select
".NET CLR Data." The SQL Server .NET data provider adds performance counters that can
tune connection pooling and troubleshoot pooling problems. Table 1-2 describes the counters.
Table 1-2. SQL Server .NET provider performance counters|
|
SqlClient: Current # of pooled and nonpooled
connections
|
Current number of connections, both pooled and non-pooled
| |
SqlClient: Current # pooled connections
|
Current number of pooled connections
| |
SqlClient: Current # connection pools
|
Current number of connection pools
| |
SqlClient: Peak # pooled connections
|
The largest number of connections in all pools since the process
started
| |
SqlClient: Total # failed connects
|
The total number of attempts to open a connection that have failed
for any reason
|
Select the counters to monitor from the list box and click the Add
button. Click the Close button.
ODBC
To enable ODBC performance monitoring:
Open the ODBC Data Source Administrator in Control Panel
Administrative Tools. Select the Connection Pooling tab. Ensure that the PerfMon Enable checkbox is checked. Start Performance Monitor by selecting Start All
Programs Administrative Tools
Performance. Add performance counters to monitor connection pooling with one of
the following methods: In the Performance object drop down list, select ODBC Connection
Pooling. Table 1-3 describes the ODBC Connection
Pooling counters.
Table 1-3. ODBC connection pooling counters|
|
Connections Currently Active
|
Number of connections currently used by applications
| |
Connections Currently Free
|
Number of connections in the pool available for requests
| |
Connections/Sec Hard
|
Number of real connections per second
| |
Connections/Sec Soft
|
Number of connections server from the pool per second
| |
Disconnections/Sec Hard
|
Number of real disconnects per second
| |
Disconnections/Sec Soft
|
Number of disconnects from the pool per second
|
 | 
