Compartilhar via

Using Provider Statistics 

  • Artigo

With Microsoft .NET Framework version 2.0, support for run-time statistics is a new feature of the .NET Framework Data Provider for SQL Server. You must enable statistics by setting the StatisticsEnabled property of the SqlConnection object to True after you have a valid connection object created. After statistics are enabled, you can review them as a "snapshot in time" by retrieving an IDictionary reference via the RetrieveStatistics method of the SqlConnection object. You enumerate through the list as a set of name/value pair dictionary entries. These name/value pairs are unordered. At any time, you can call the ResetStatistics method of the SqlConnection object to reset the counters. If statistic gathering has not been enabled, an exception is not generated. In addition, if RetrieveStatistics is called without StatisticsEnabled having been called first, the values retrieved are the initial values for each entry. If you enable statistics, run your application for a while, and then disable statistics, the values retrieved will reflect the values collected up to the point where statistics were disabled. All statistical values gathered are on a per-connection basis.

Statistical Values Available

Currently there are 18 different items available from the Microsoft SQL Server provider. The number of items available can be accessed via the Count property of the IDictionary interface reference returned by RetrieveStatistics. All of the counters for provider statistics use the common language runtime Int64 type (long in C# and Visual Basic), which is 64 bits wide. The maximum value of the int64 data type, as defined by the int64.MaxValue field, is ((2^63)-1)). When the values for the counters reach this maximum value, they should no longer be considered accurate. This means that int64.MaxValue-1 ((2^63)-2) is effectively the greatest valid value for any statistic.

Note

A dictionary is used for returning provider statistics because the number, names and order of the returned statistics may change in the future. Applications should not rely on a specific value being found in the dictionary, but should instead check whether the value is there and branch accordingly.

The following table describes the current statistical values available. Note that the key names for the individual values are not localized across regional versions of the Microsoft .NET Framework.

Key Name Description

BuffersReceived

Returns the number of tabular data stream (TDS) packets received by the provider from SQL Server after the application has started using the provider and has enabled statistics.

BuffersSent

Returns the number of TDS packets sent to SQL Server by the provider after statistics have been enabled. Large commands can require multiple buffers. For example, if a large command is sent to the server and it requires six packets, ServerRoundtrips is incremented by one and BuffersSent is incremented by six.

BytesReceived

Returns the number of bytes of data in the TDS packets received by the provider from SQL Server once the application has started using the provider and has enabled statistics.

BytesSent

Returns the number of bytes of data sent to SQL Server in TDS packets after the application has started using the provider and has enabled statistics.

ConnectionTime

The amount of time that the connection has been opened after statistics have been enabled (total connection time if statistics were enabled before opening the connection).

CursorOpens

Returns the number of times a cursor was open through the connection once the application has started using the provider and has enabled statistics.

Note that read-only/forward-only results returned by SELECT statements are not considered cursors and thus do not affect this counter.

ExecutionTime

Returns the cumulative amount of time the provider has spent processing once statistics have been enabled, including the time spent waiting for replies from the server as well as the time spent executing code in the provider itself.

The classes that include timing code are:

SqlConnection

SqlCommand

SqlDataReader

SqlDataAdapter

SqlTransaction

SqlCommandBuilder

To keep performance-critical members as small as possible, the following members are not timed:

SqlDataReader

this[] operator (all overloads)

GetBoolean

GetChar

GetDateTime

GetDecimal

GetDouble

GetFloat

GetGuid

GetInt16

GetInt32

GetInt64

GetName

GetOrdinal

GetSqlBinary

GetSqlBoolean

GetSqlByte

GetSqlDateTime

GetSqlDecimal

GetSqlDouble

GetSqlGuid

GetSqlInt16

GetSqlInt32

GetSqlInt64

GetSqlMoney

GetSqlSingle

GetSqlString

GetString

IsDBNull

IduCount

Returns the total number of INSERT, DELETE, and UPDATE statements executed through the connection once the application has started using the provider and has enabled statistics.

IduRows

Returns the total number of rows affected by INSERT, DELETE, and UPDATE statements executed through the connection once the application has started using the provider and has enabled statistics.

NetworkServerTime

Returns the cumulative amount of time the provider spent waiting for replies from the server once the application has started using the provider and has enabled statistics.

PreparedExecs

Returns the number of prepared commands executed through the connection once the application has started using the provider and has enabled statistics.

Prepares

Returns the number of statements prepared through the connection once the application has started using the provider and has enabled statistics.

SelectCount

Returns the number of SELECT statements executed through the connection once the application has started using the provider and has enabled statistics. This includes FETCH statements to retrieve rows from cursors, and the count for SELECT statements is updated when the end of a SqlDataReader is reached.

SelectRows

Returns the number of rows selected once the application has started using the provider and has enabled statistics. This counter reflects all the rows generated by SQL statements, even those that were not actually consumed by the caller. For example, closing a data reader before reading the entire result set would not affect the count. This includes the rows retrieved from cursors through FETCH statements.

ServerRoundtrips

Returns the number of times the connection sent commands to the server and got a reply back once the application has started using the provider and has enabled statistics.

SumResultSets

Returns the number of result sets that have been used once the application has started using the provider and has enabled statistics. For example this would include any result set returned to the client. For cursors, each fetch or block-fetch operation is considered an independent result set.

Transactions

Returns the number of user transactions started once the application has started using the provider and has enabled statistics, including rollbacks. If a connection is running with auto commit on, each command is considered a transaction.

This counter increments the transaction count as soon as a BEGIN TRAN statement is executed, regardless of whether the transaction is committed or rolled back later.

UnpreparedExecs

Returns the number of unprepared statements executed through the connection once the application has started using the provider and has enabled statistics.

Retrieving a Value

The following console application shows how to enable statistics on a connection, retrieve four individual statistic values, and write them out to the console window.

Note

The following example uses the sample AdventureWorks database included with SQL Server 2005. The connection string provided in the sample code assumes the database is installed and available on the local computer. Modify the connection string as necessary for your environment.

Option Strict On

Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient

Module Module1

  Sub Main()
    Dim connectionString As String = GetConnectionString()
    
    Using awConnection As New SqlConnection(connectionString)
      ' StatisticsEnabled is False by default.
      ' It must be set to True to start the 
      ' statistic collection process.
      awConnection.StatisticsEnabled = True

      Dim productSQL As String = "SELECT * FROM Production.Product"
      Dim productAdapter As _
          New SqlDataAdapter(productSQL, awConnection)

      Dim awDataSet As New DataSet()

      awConnection.Open()

      productAdapter.Fill(awDataSet, "ProductTable")

      ' Retrieve the current statistics as
      ' a collection of values at this point
      ' and time.
      Dim currentStatistics As IDictionary = _
          awConnection.RetrieveStatistics()

      Console.WriteLine("Total Counters: " & _
          currentStatistics.Count.ToString())
      Console.WriteLine()

      ' Retrieve a few individual values
      ' related to the previous command.
      Dim bytesReceived As Long = _
          CLng(currentStatistics.Item("BytesReceived"))
      Dim bytesSent As Long = _
          CLng(currentStatistics.Item("BytesSent"))
      Dim selectCount As Long = _
          CLng(currentStatistics.Item("SelectCount"))
      Dim selectRows As Long = _
          CLng(currentStatistics.Item("SelectRows"))

      Console.WriteLine("BytesReceived: " & bytesReceived.ToString())
      Console.WriteLine("BytesSent: " & bytesSent.ToString())
      Console.WriteLine("SelectCount: " & selectCount.ToString())
      Console.WriteLine("SelectRows: " & selectRows.ToString())

      Console.WriteLine()
      Console.WriteLine("Press any key to continue")
      Console.ReadLine()
    End Using

  End Sub

  Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,
    ' you can retrive it from a configuration file.
    Return "Data Source=localhost;Integrated Security=SSPI;" & _
      "Initial Catalog=AdventureWorks"
  End Function
End Module

[C#]

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace CS_Stats_Console_GetValue
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectionString = GetConnectionString();

      using (SqlConnection awConnection = 
        new SqlConnection(connectionString))
      {
        // StatisticsEnabled is False by default.
        // It must be set to True to start the 
        // statistic collection process.
        awConnection.StatisticsEnabled = true;

        string productSQL = "SELECT * FROM Production.Product";
        SqlDataAdapter productAdapter = 
          new SqlDataAdapter(productSQL, awConnection);

        DataSet awDataSet = new DataSet();

        awConnection.Open();

        productAdapter.Fill(awDataSet, "ProductTable");
        // Retrieve the current statistics as
        // a collection of values at this point
        // and time.
        IDictionary currentStatistics =
          awConnection.RetrieveStatistics();

        Console.WriteLine("Total Counters: " +
          currentStatistics.Count.ToString());
        Console.WriteLine();

        // Retrieve a few individual values
        // related to the previous command.
        long bytesReceived =
            (long) currentStatistics["BytesReceived"];
        long bytesSent =
            (long) currentStatistics["BytesSent"];
        long selectCount =
            (long) currentStatistics["SelectCount"];
        long selectRows =
            (long) currentStatistics["SelectRows"];

        Console.WriteLine("BytesReceived: " +
            bytesReceived.ToString());
        Console.WriteLine("BytesSent: " +
            bytesSent.ToString());
        Console.WriteLine("SelectCount: " +
            selectCount.ToString());
        Console.WriteLine("SelectRows: " +
            selectRows.ToString());

        Console.WriteLine();
        Console.WriteLine("Press any key to continue");
        Console.ReadLine();
      }

    }
    private static string GetConnectionString()
    {
      // To avoid storing the connection string in your code,
      // you can retrive it from a configuration file.
      return "Data Source=localhost;Integrated Security=SSPI;" + 
        "Initial Catalog=AdventureWorks";
    }
  }
}

Retrieving All Values

The following console application shows how to enable statistics on a connection, retrieve all available statistic values using the enumerator, and write them to the console window.

Note

The following example uses the sample AdventureWorks database included with SQL Server 2005. The connection string provided in the sample code assumes the database is installed and available on the local computer. Modify the connection string as necessary for your environment.

Option Strict On

Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient

Module Module1
  Sub Main()
    Dim connectionString As String = GetConnectionString()

    Using awConnection As New SqlConnection(connectionString)
      ' StatisticsEnabled is False by default.
      ' It must be set to True to start the 
      ' statistic collection process.
      awConnection.StatisticsEnabled = True

      Dim productSQL As String = "SELECT * FROM Production.Product"
      Dim productAdapter As _
          New SqlDataAdapter(productSQL, awConnection)

      Dim awDataSet As New DataSet()

      awConnection.Open()

      productAdapter.Fill(awDataSet, "ProductTable")

      ' Retrieve the current statistics as
      ' a collection of values at this point
      ' and time.
      Dim currentStatistics As IDictionary = _
          awConnection.RetrieveStatistics()


      Console.WriteLine("Total Counters: " & _
          currentStatistics.Count.ToString())
      Console.WriteLine()

      Console.WriteLine("Key Name and Value")

      ' Note the entries are unsorted.
      For Each entry As DictionaryEntry In currentStatistics
        Console.WriteLine(entry.Key.ToString() & _
            ": " & entry.Value.ToString())
      Next

      Console.WriteLine()
      Console.WriteLine("Press any key to continue")
      Console.ReadLine()
    End Using

  End Sub

  Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,
    ' you can retrive it from a configuration file.
    Return "Data Source=localhost;Integrated Security=SSPI;" & _
      "Initial Catalog=AdventureWorks"
  End Function
End Module

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CS_Stats_Console_GetAll
{
  class Program
  {
    static void Main(string[] args)
    {
      string connectionString = GetConnectionString();

      using (SqlConnection awConnection = 
        new SqlConnection(connectionString))
      {
        // StatisticsEnabled is False by default.
        // It must be set to True to start the 
        // statistic collection process.
        awConnection.StatisticsEnabled = true;

        string productSQL = "SELECT * FROM Production.Product";
        SqlDataAdapter productAdapter =
            new SqlDataAdapter(productSQL, awConnection);

        DataSet awDataSet = new DataSet();

        awConnection.Open();

        productAdapter.Fill(awDataSet, "ProductTable");

        // Retrieve the current statistics as
        // a collection of values at this point
        // and time.
        IDictionary currentStatistics =
            awConnection.RetrieveStatistics();

        Console.WriteLine("Total Counters: " +
            currentStatistics.Count.ToString());
        Console.WriteLine();

        Console.WriteLine("Key Name and Value");

        // Note the entries are unsorted.
        foreach (DictionaryEntry entry in currentStatistics)
        {
          Console.WriteLine(entry.Key.ToString() +
              ": " + entry.Value.ToString());
        }

        Console.WriteLine();
        Console.WriteLine("Press any key to continue");
        Console.ReadLine();
      }

    }
    private static string GetConnectionString()
    {
      // To avoid storing the connection string in your code,
      // you can retrive it from a configuration file.
      return "Data Source=localhost;Integrated Security=SSPI;" + 
        "Initial Catalog=AdventureWorks";
    }
  }
}

See Also

Other Resources

Using the .NET Framework Data Provider for SQL Server