heartstill

博客园 首页 新随笔 联系 订阅 管理
  207 随笔 :: 0 文章 :: 23 评论 :: 17万 阅读

Monitoring Pooling

You can monitor connection pooling to determine that it is working as expected and to help you identify the best minimum and maximum pool sizes.

Monitoring Pooling on a Computer that is Running SQL Server

You can monitor the number of open connections to SQL Server by using the SQL Server SQLServer:General Statistics performance counter object. This object is available only on a computer that is running SQL Server.

The connections are not specific to one particular application. If there are multiple applications accessing the server, this object reflects the total number of open connections for every application. Figure 12.2 shows the SQLServer:General Statistics object in the Performance Monitor tool.

Ff647768.ch12-sql-server-general-statistics(en-us,PandP.10).gif

Figure 12.2: Performance monitor showing the SQLServer:General Statistics counter

When monitoring SQLServer:General Statistics, you should observe the following:

  • The number of logins per second increases during application startup when the connection pool is established. The number of logins per second should then drop to zero and stay there. Repeated logins and logouts per second indicate that the connection pool is not being used because a different security context is being used to establish the connection.
  • The User Connections value should stabilize and remain constant. If this value increases and you see a jagged pattern in the number of logins per second, you may be experiencing a connection leak in the connection pool.

Monitoring Pooling Using the .NET Framework

The .NET Framework Data Provider for SQL Server provides several counters. The following counters are of particular significance:

  • SqlClient: Current # connection pools
  • SqlClient: Current # pooled and nonpooled connections
  • SqlClient: Current # pooled connections
  • SqlClient: Peak # pooled connections

The SqlClient: Current # connection pools counter indicates the number of connection pools that are currently in use. A large number of pools indicates that a pool is not being shared across clients. Using different connection strings creates new pools.

The SqlClient: Peak # pooled connections counter indicates the maximum number of connections that are currently in use. If this value remains at its peak, consider measuring the performance impact of increasing the Max Pool Size attribute in your connection string. The default value is 100. If you see this value at its peak in conjunction with a high number of failed connections in the SqlClient: Total # failed connects counter, consider changing the value and monitoring performance.

Note   These SqlClient counters may not be reset in .NET Framework version 1.1 when you stop and then restart an application. To reset the counters, stop the application and exit System Monitor, and then start the application and System Monitor again.
posted on   开始测试  阅读(278)  评论(0编辑  收藏  举报
点击右上角即可分享
微信分享提示