ADO.NET 3.5 Cookbook, 2nd Edition

1.21. 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.17 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 → Performance Tools → SQL Server Profiler.

   • From SQL Server Management Studio: Tools → SQL Server Profiler.

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

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

4. Select the Events Selection 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.

7. Figure 1-22 shows the result of the trace after a database connection is opened and closed.

Figure 1-22. SQL Server Profiler trace

Windows Performance Monitor. To use the Windows Performance Monitor to monitor connection pooling:

1. Start Performance Monitor by selecting Start → All Programs → Administrative Tools → Performance.

2. Right-click the graph and select Properties from the context menu to open the System Monitor Properties dialog. Select the Graph tab and set the "Vertical scale maximum" to 2. This will make the results easier to see. Click OK to close the dialog.

3. Add performance counters to monitor connection pooling with one of the following methods:

   • Right-click the graph and select Add Counters from the pop-up menu.

   • Click the Add button above the graph.

   Both of these actions open the Add Counters dialog shown in Figure 1-23.

   

   Figure 1-23. Add Counters dialog

4. In the "Performance object" drop-down list, select SQLServer:General Statistics. Performance counters can help tune connection pooling and troubleshoot pooling problems. To get an explanation for a counter, select the counter and click the Explain button.

5. Select the User Connections counter from the list and click the Add button. Click the Close button.

6. Figure 1-24 shows the Performance Monitor as a user connects to and disconnects from SQL Server.

Figure 1-24. Performance Monitor display of user connections

ODBC

To enable ODBC performance monitoring:

1. Open the ODBC Data Source Administrator by selecting Start → All Programs → Administrative Tools → Data Sources (ODBC) as shown in Figure 1-25.

2. Select the Connection Pooling tab.

3. Ensure that the PerfMon Enable checkbox is checked. Click OK to close the dialog.

4. Start Performance Monitor by selecting Start → All Programs → Administrative Tools → Performance.

5. Add performance counters to monitor connection pooling with one of the following methods:

   • Right-click the graph and select Add Counters from the pop-up menu.

   • Click the Add button above the graph.

   Both of these actions open the Add Counters dialog shown in Figure 1-26.

6. In the "Performance object" drop-down list, select ODBC Connection Pooling. Table 1-7 describes the available ODBC connection pooling counters.

Figure 1-25. ODBC Data Source Administrator dialog

Figure 1-26. Add Counters dialog

Table 1-7. ODBC connection pooling counters

Counter	Description
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 from the pool per second
Disconnections/Sec Hard	Hard Number of real disconnects per second
Disconnections/Sec Soft	Number of disconnects from the pool per second