ADO.NET Cookbook

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.

SQL Server Profiler

To use the SQL Server Profiler to monitor connection pooling:

1. Start the Profiler using one of the following methods

   • From Windows desktop: Start → All Programs → Microsoft SQL Server → Profiler.

   • From SQL Enterprise Manager: Tools → SQL Profiler.

2. When the SQL Server Profiler appears, select File → New → Trace.

3. Supply connection details and click OK. The Trace Properties dialog box will appear.

4. Select the Events tab of the Trace Properties dialog box.

5. 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.

6. The new Profiler window will display a table containing Audit Login events when connections are established and Audit Logout events when connections are closed.

Windows Performance ...