.Net SQL Data Provider Test

A data provider in the .NET Framework serves as a bridge between an application and a data source. A .NET Framework data provider enables you to return query results from a data source, execute commands at a data source, and propagate changes in a DataSet to a data source.

The .Net Data Provider for SQL Server allows you to connect to Microsoft SQL Server 7.0, 2000, and 2005 databases, and perform the above-mentioned operations. This test reports many useful metrics that shed light on the health of the interactions between the ASP .Net sever and the SQL server.

Target of the test : The ASP .Net server

Agent deploying the test : An internal agent;

Outputs of the test : One set of results for the ASP .Net server being monitored

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed.
  2. Host - The host for which the test is to be configured.
  3. Port - The port to which the specified host listens.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Hard connects:

Indicates the number of actual connections per second that are being made to a database server.

Connects/Sec

 

Hard disconnects:

Indicates the number of actual disconnects per second that are being made to a database server.

Disconnects/Sec

 

Active connection pool groups:

Indicates the number of currently active connection pool groups.

Number

The value of this measure is controlled by the number of unique connection strings that are found in the AppDomain.

 

Active connection pools:

Indicates the number of connection pools that are currently active.

Number

When a connection is first opened, a connection pool is created based on matching criteria that associates the pool with the connection string in the connection. Each connection pool is associated with a distinct connection string. If the connection string is not an exact match to an existing pool when a new connection is opened, a new pool is created. Connections are pooled per process, per application domain, per connection string, and, when integrated security is used, per Windows identity.

When using Windows Authentication (integrated security), both the Active connection pool groups and Active connection pools measures are significant. The reason is that connection pool groups map to unique connection strings. When integrated security is used, connection pools map to connection strings and additionally create separate pools for individual Windows identities. For example, if Fred and Julie, each within the same AppDomain, both use the connection string "Data Source=MySqlServer;Integrated Security=true", a connection pool group is created for the connection string, and two additional pools are created, one for Fred and one for Julie. If John and Martha use a connection string with an identical SQL Server login, "Data Source=MySqlServer;User Id=lowPrivUser;Password=Strong?Password", then only a single pool is created for the lowPrivUser identity.

Active connections:

Indicates the number of connections that are currently in use.

 

Number

 

Free connections:

Indicates the count of unused connections.

Number

Ideally, the value of this measure. A very low value indicates excessive connection usage.

Inactive connection pools:

Indicates the number of connection pools that have had no recent activity and are waiting to be disposed.

Number

 

Inactive connection pool groups:

Indicates the number of inactive connection pool groups that were waiting to be deactivated i.e., to be pruned.

Number

 

Non-pooled connections:

Indicates the number of active connections that are not using any of the connection pools.

Number

 

Pooled connections:

Indicates the number of connections that are managed by the connection pooler.

Number

 

Reclaimed connections:

Indicates the number of connections that have been reclaimed through garbage collection where Close or Dispose was not called by the application.

Number

Not explicitly closing or disposing connections hurts performance.

Waiting connections:

Indicates the number of connections that are currently awaiting completion of an action and are therefore unavailable for use by any other application.

Number

 

Soft connects:

Indicates the rate at which connections are pulled from the connection pool.

Connects/Sec

 

Soft disconnects:

Indicates the rate at which connections are returned to the connection pool.

Disconnects/Sec